SQL Insert Into using VBA with where criteria

jm112

Registered User.
Local time
Today, 04:31
Joined
Nov 5, 2010
Messages
14
Hi all,

Been trying to get this piece of VBA code to work but not sure why it isn't.

This SELECT statement works fine:

Code:
   strSQL = "SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
   "].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"

However when I try to use this statement to create a new table like this:

Code:
  strSQL = "INSERT INTO [" & strNewTable & "] SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
   "].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"
 
   db.Execute strSQL

I get the "Too few parameters. Expected 1." error.

Why would the syntax for the WHERE criteria not work when going from a SELECT to an INSERT INTO using the same SELECT statement that works independantly as a row source?

Thanks in advance for any guidance.
 
Does the two tables have the similar structure? Equal number of Columns? If not there is your problem.. If yes, then you might as well include all the Field names explicitly written down..
 
Does the two tables have the similar structure? Equal number of Columns? If not there is your problem.. If yes, then you might as well include all the Field names explicitly written down..

Thanks for the response.

strNewTable doesn't exist yet. It's being created with the SQL statement.

Code:
strSQL = "INSERT INTO [" & strNewTable & "] SELECT [" & strTmp & "].* FROM [" & strTmp & "]"
db.Execute strSQL

Works fine in creating the new table with data but it doesn't include the WHERE criteria I need.
 
I like to create dummy SQLs using a query builder, then view as SQL, and change the names to the relevant variables...
 
AIR CODED !!
Code:
    strSQL = "SELECT [" & strTmp & "].* INTO [" & strNewTable & "] FROM [" & strTmp & "] WHERE [" & strTmp & _
             "].[Work center] Like '*' & [Forms]![frmImport]![txtCode] & '*';"
 
   db.Execute strSQL
 
I like to create dummy SQLs using a query builder, then view as SQL, and change the names to the relevant variables...

Yeah, this is what I usually do and it works fine in query design, just it's not working in VBA for whatever reason.
 

Users who are viewing this thread

Back
Top Bottom