Getting Different Results: Query in VBA VS same in Query Wizard

[rokuk]

Hi there!

I am trying to run the following simple query from within VBA:

"

"INSERT INTO tempTable SELECT * FROM Deletions"

"


When I run this from the Access 2003 query wizard in SQL view, it executes just fine. When I move to my form code and try running it with:

DoCmd.RunSQL "INSERT INTO tempTable SELECT * FROM Deletions"

or

CurrentDb.execute "INSERT INTO tempTable SELECT * FROM Deletions"

It does not work. I've also tried this by assigning the queries to string variables and using DoCmd.RunSql and CurrentDb.execute on the string varaibles, but that makes no difference. The DoCmd will give me the notice it is going to append 0 (zero) rows - while running the same query in the SQL view of the wizard gives me notice it is going to append the correct number of rows. I am trying to automate this as part of a process, so I really want to do this from VBA and not have to go through the Access queries / wizard each time. Is there any reason why a query would act differently between the SQL view of the wizard and the VBA methods here?

Thanks!

[puppydogbuddy]

Hi there!

I am trying to run the following simple query from within VBA:

"

"INSERT INTO tempTable SELECT * FROM Deletions"

"


When I run this from the Access 2003 query wizard in SQL view, it executes just fine. When I move to my form code and try running it with:

DoCmd.RunSQL "INSERT INTO tempTable SELECT * FROM Deletions"

or

CurrentDb.execute "INSERT INTO tempTable SELECT * FROM Deletions"

It does not work. I've also tried this by assigning the queries to string variables and using DoCmd.RunSql and CurrentDb.execute on the string varaibles, but that makes no difference. The DoCmd will give me the notice it is going to append 0 (zero) rows - while running the same query in the SQL view of the wizard gives me notice it is going to append the correct number of rows. I am trying to automate this as part of a process, so I really want to do this from VBA and not have to go through the Access queries / wizard each time. Is there any reason why a query would act differently between the SQL view of the wizard and the VBA methods here?

Thanks!

An SQL statement must finish with a semicolon (;). If you omit the semicolon when writing an SQL statement in the SQL view of the Access query design window your query will still work because Access corrects the error for you! You must remember to include it when writing SQL in VBA and elsewhere.


Change this:

CurrentDb.execute "INSERT INTO tempTable SELECT * FROM Deletions"

To (note the semi-colon at the end)

CurrentDb.execute "INSERT INTO tempTable SELECT * FROM Deletions;"