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
– GarethD, Commented May 28, 2013 at 14:05

RockResolve · Accepted Answer · 2013-05-29 22:58:34Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

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

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

Comments

Your Answer

Sign up or log in

Post as a guest

Related