Access VBA SQL INSERT INTO -> Too Few Parameters, Expected 2

Question

I have tried googling this but to no avail - I am not quite understanding what I am doing wrong with this SQL code. I am currently at a loss. What does this error mean and what is my likely culprit?

As you can see, I am trying to insert into a processing table from 2 original tables with only 1 simple WHERE condition. Any help is appreciated!

CurrentDb.Execute "
        INSERT INTO Processing
          ([State], [Zip Code], [Gross Sales], [Sales Exempt Type], 
           [Sales Exempt Amount], [Taxable], [Tax], BATCH_NO, FILENAME,FILEPATH, 
           USERNAME, IMPORT_TIMESTAMP)" & _
            "
        SELECT [tblImport].[State/ Province]
              , [tblImport].[Dest Postal Code]
              , [tblImport].[Line Amount]
              , [tblImport].[Jurisdiction Description]
              , [tblImport].[Line Exempt]
              , [tblImport].[Line Taxable Amount]
              , [tblImport].[Tax Amount]
              , [batch].BATCH_NO
              , [batch].FILENAME
              , [batch].FILEPATH
              , [batch].IMPORT_USERNAME
              , [batch].IMPORT_TIMESTAMP " & _
            "
          FROM batch
               , tblImport
         WHERE  BATCH_NO =" & li_batch_no

What is the data type of BATCH_NO? Another cause of this error can be column names that contain special characters or are reserved words that are not qualified properly ([]), I can't see any obvious culprits, but it is probably worth double checking. — GarethD

RockResolve RockResolve · Accepted Answer · 2013-05-29T22:58:34

It is likely to be an invalid Select table or column.

Use Access's own Query designer to help you find it.

First get the sql as run by the code. Put a breakpoint in the code, copy the sql to the immediate window and prefix it with a "?" and run it i.e. after copying the code: [CTRL-G]?[CTRL-V][RETURN]. Copy the returned sql.

Create a new Query, change to Sql view, paste your evaluated sql.

Change to Design view. Anything unexpected? Has it changed any fields to Expressions ("Expr" prefix in Field) - because it could not find a table or column in your sql?

Change back to Sql view. Has it changed your Sql attempting to resolve tables/fields? Any Expr aliases are unresolved tables/columns.

Change to Datasheet View to run the Select (but not the Insert). Any prompts for parameters are for table/column names Access could not resolve.

Lastly Run the Query. Again any prompts for parameters are for table/column names Access could not resolve. Any errors encountered will be presented in friendlier messageboxes rather than your Execute code's Err object.

Access VBA SQL INSERT INTO -> Too Few Parameters, Expected 2

1 Answers

Use Access's own Query designer to help you find it.