By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

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

P: 1
Hi there!

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

"
Expand|Select|Wrap|Line Numbers
  1. "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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tempTable SELECT * FROM Deletions"
or
Expand|Select|Wrap|Line Numbers
  1. 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!
Dec 21 '07 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
Hi there!

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

"
Expand|Select|Wrap|Line Numbers
  1. "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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tempTable SELECT * FROM Deletions"
or
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.execute "INSERT INTO tempTable SELECT * FROM Deletions"

To (note the semi-colon at the end)
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.execute "INSERT INTO tempTable SELECT * FROM Deletions;"
Dec 22 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.