MS-Access Too Few Parameters. Expected 3

Question

I import a csv file that updates existing table data or inserts it if matching data doesn't already exist. This worked perfectly for awhile, then I added three fields (Back, Forward, Exit--in response to the underlying data changing), and now I'm getting errors.

The code throwing errors:

    strTest = "update tblStories INNER JOIN tblTemp2 ON format(tblTemp2.[Time Posted],'MM/dd/yyyy hh:mm:ss') = format(tblStories.[Time Posted] ,'MM/dd/yyyy hh:mm:ss') set tblStories.[Completion Rate]=tblTemp2.[Completion Rate],tblStories.[Avg Views/User]=tblTemp2.[Avg Views/User],tblStories.[Impressions]=tblTemp2.[Impressions],tblStories.[Reach]=tblTemp2.[Reach],tblStories.[Reply Count]=tblTemp2.[Reply Count],tblStories.[Back]=tblTemp2.[Back],tblStories.[Forward]=tblTemp2.[Forward],tblStories.[Exit]=tblTemp2.[Exit]"
    ExecuteSQL (strTest)
    strTest = "insert into tblStories ([Image URL],[Completion Rate],[Avg Views/User],[Impressions],[Reach],[Reply Count],[Back],[Forward],[Exit],[Time Posted]) SELECT tblTemp2.[Image URL],tblTemp2.[Completion Rate],tblTemp2.[Avg Views/User],tblTemp2.[Impressions],tblTemp2.[Reach],tblTemp2.[Reply Count],tblTemp2.[Back],tblTemp2.[Forward],tblTemp2.[Exit],tblTemp2.[Time Posted] FROM tblTemp2 LEFT JOIN tblStories ON  format(tblTemp2.[Time Posted],'MM/dd/yyyy hh:mm:ss') = format(tblStories.[Time Posted] ,'MM/dd/yyyy hh:mm:ss')  WHERE (((tblStories.[Time Posted]) Is Null))"
    ExecuteSQL (strTest)

When I interrupt to print and confirm, I get the following:

    update tblStories INNER JOIN tblTemp2 ON format(tblTemp2.[Time Posted],'MM/dd/yyyy hh:mm:ss') = format(tblStories.[Time Posted] ,'MM/dd/yyyy hh:mm:ss') set tblStories.[Completion Rate]=tblTemp2.[Completion Rate],tblStories.[Avg Views/User]=tblTemp2.[Avg Views/User],tblStories.[Impressions]=tblTemp2.[Impressions],tblStories.[Reach]=tblTemp2.[Reach],tblStories.[Reply Count]=tblTemp2.[Reply Count], tblStories.[Back]=tblTemp2.[Back], tblStories.[Forward]=tblTemp2.[Forward], tblStories.[Exit]=tblTemp2.[Exit]

Thoughts?

What is the data type of [Time Posted] field in either table? Short text or date/time? — Parfait

Parfait Parfait · Accepted Answer · 2020-10-07T21:40:57

Essentially, the translation of this error means three unknown columns. Any foreign identifier in a query to the MS Access engine is considered a parameter which is not used in your action calls. As written, the three new columns ([Back], [Forward], [Exit]) must exist in BOTH tables (not just one). Error suggests one of the tables is missing these columns.

Remember, Data Manipulation Language (DML) queries in SQL does not adjust the structure of tables. So UPDATE and INSERT does not add new columns. These queries along with DELETE only adjust (add/edit/remove) data rows. On the other hand, Data Definition Language (DDL) queries in SQL does adjust the structure of tables. But these are different commands (CREATE, ALTER, DROP).

Altogether, before attempting to run these queries, make sure all specified columns exist beforehand. Finally, if [Time Posted] is a date field, Format() is not necessary. Below illustrates in better format (do not include comments in Access queries).

UPDATE tblStories 
INNER JOIN tblTemp2 
   ON tblTemp2.[Time Posted] = tblStories.[Time Posted]

SET  tblStories.[Completion Rate] = tblTemp2.[Completion Rate]
   , tblStories.[Avg Views/User] = tblTemp2.[Avg Views/User]
   , tblStories.[Impressions] = tblTemp2.[Impressions]
   , tblStories.[Reach] = tblTemp2.[Reach]
   , tblStories.[Reply Count] = tblTemp2.[Reply Count]
   , tblStories.[Back] = tblTemp2.[Back]                   -- COLUMNS MUST EXIST IN BOTH
   , tblStories.[Forward] = tblTemp2.[Forward]             -- COLUMNS MUST EXIST IN BOTH
   , tblStories.[Exit] = tblTemp2.[Exit]                   -- COLUMNS MUST EXIST IN BOTH

INSERT INTO tblStories ([Image URL], [Completion Rate], [Avg Views/User],
                        [Impressions],[Reach], [Reply Count], 
                        [Back], [Forward], [Exit], [Time Posted])  -- COLUMNS MUST EXIST IN TABLE
SELECT tblTemp2.[Image URL]
     , tblTemp2.[Completion Rate]
     , tblTemp2.[Avg Views/User]
     , tblTemp2.[Impressions]
     , tblTemp2.[Reach]
     , tblTemp2.[Reply Count]
     , tblTemp2.[Back]                                     -- COLUMN MUST EXIST IN TABLE
     , tblTemp2.[Forward]                                  -- COLUMN MUST EXIST IN TABLE
     , tblTemp2.[Exit]                                     -- COLUMN MUST EXIST IN TABLE
     , tblTemp2.[Time Posted] 

FROM tblTemp2 
LEFT JOIN tblStories 
     ON tblTemp2.[Time Posted] = tblStories.[Time Posted] 
WHERE tblStories.[Time Posted] IS NULL

If [Time Posted] is a short text field, simply run CDate for joining:

ON CDate(tblTemp2.[Time Posted]) = CDate(tblStories.[Time Posted])

MS-Access Too Few Parameters. Expected 3

1 Answers