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

Make query works stand alone but not in SQL

P: 49
Writing some VBA to call a make table query. It the make query is pulling data from a MS 2008 R2 SQL database then creating the data locally for processing. It works, but not when I call it out of the code below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyData_Click()
  2.  
  3. Dim dbo_Machine_a As QueryDef
  4. Dim dbo_Machine_Hello As QueryDef
  5. Dim dbo_Machine_World As QueryDef
  6.  
  7. Select Case MachineNameCombo
  8.  
  9.     Case "a"
  10.             DoCmd.SetWarnings False
  11.             DBEngine(0)(0).Execute "dbo_Machine_a"
  12.             DoCmd.SetWarnings True
  13.  
  14.  
  15.     Case "Hello"
  16.             DoCmd.SetWarnings False
  17.             DBEngine(0)(0).Execute "dbo_Machine_Hello"
  18.             DoCmd.SetWarnings True
  19.  
  20.     Case "World"
  21.  
  22.             DoCmd.SetWarnings False
  23.             DBEngine(0)(0).Execute "dbo_Machine_World"
  24.             DoCmd.SetWarnings True
  25.  
  26. End Select
  27.  
  28. End Sub
  29.  
I get the error: Run time error '3065' Can not execute a select query. From what I read on line about a 3065 I should be able to run a make query, just not a "select"
Dec 28 '11 #1

✓ answered by Stewart Ross

Please replace DoCmd.RunSQL with DoCmd.OpenQuery - the RunSQL method would require the SQL itself it turns out, not the name of the query.

OpenQuery will run action queries just as if you were running them from the normal query environment, with the exception that with SetWarnings off you will not have to confirm the replacement of the table.

Taking one of the Case statements as an example, you should end up with:

Expand|Select|Wrap|Line Numbers
  1. Case "World"
  2.    DoCmd.SetWarnings False
  3.    DoCmd.OpenQuery "dbo_Machine_World2"
  4.    DoCmd.SetWarnings True
  5.  
-Stewart

Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
I see a number of problems with your approach. Firstly, the Execute method of the database object is intended to work with SQL strings (or the name of a query), not querydefs. Secondly, if you have included all of the code for your copydata_click sub, you have not set the locally-defined querydefs at all (that is, you have not associated the querydef variables with whatever actual queries you want to execute).

If you are using DAO querydefs you can use the querydef's Execute method to execute the query (after setting the querydef to associate it with the correct query, of course).

It appears to me to be much simpler if you delete your querydef local variables defined in lines 3 to 5 and simply provide the name for each query within the Execute statement in each case branch:

Expand|Select|Wrap|Line Numbers
  1. CurrentDB.Execute "Your Query Name Goes Here"
If you use Execute you can dispense with the setwarnings statements, which are not required in this case. However, in the case of a make-table query where a table of the same name already exists Execute will fail with a run-time error. This is a different behaviour than would occur where the Setwarnings statements are really needed, which is with DoCmd.RunSQL:

Expand|Select|Wrap|Line Numbers
  1. SetWarnings False
  2. DoCmd.RunSQL "Your Query Name Goes Here"
  3. SetWarnings True
Make-table queries run by DoCmd.RunSQL will overwrite any existing version of the table concerned. If warnings are set off (as above) this is done without the user being asked to confirm the replacement.

-Stewart
Dec 28 '11 #2

P: 49
Did not work, Here is the code after editing:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub CopyData_Click()
  3.  
  4. Select Case MachineNameCombo
  5.  
  6.     Case "a"
  7.             DoCmd.SetWarnings False
  8.             DoCmd.RunSQL "dbo_Machine_a2"
  9.             DoCmd.SetWarnings True
  10.  
  11.     Case "Hello"
  12.             DoCmd.SetWarnings False
  13.             DoCmd.RunSQL ("dbo_Machine_Hello2")
  14.             DoCmd.SetWarnings True
  15.  
  16.     Case "World"
  17.             DoCmd.SetWarnings False
  18.             DoCmd.RunSQL "dbo_Machine_World2"
  19.             DoCmd.SetWarnings True
  20.  
  21. End Select
  22.  
  23. End Sub
  24.  
I'm getting a run time error 3129 now "Invalid SQL Statement; expected 'DELETE', 'INSERT', ect...

I added the DoCmd. for the setwarning as the code was erroring out without it. I also changed the name of the make query by adding the "2" at the end. The name of the dbo file was the same as the make query name.
Dec 31 '11 #3

Expert Mod 2.5K+
P: 2,545
Please post the SQL for at least one of the queries (dbo_Machine_a2 etc). DoCmd.RunSQL, like the Execute method, is expecting to run an action query (one which contains statements beginning INSERT, DELETE etc) - and what you have posted suggests that a SELECT statement is being supplied instead.

Sorry I missed out the DoCmd off the Setwarnings statements last time.

-Stewart
Dec 31 '11 #4

P: 49
I'm using MS Access as the front end. There is no "code" what I'm tring to execute is a make table query created with MS Access standard query options.

Went into Access and viewed in SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT dbo_Machine_Hello.Simulation_Examples_Functions_Ramp1_VALUE, dbo_Machine_Hello.Machine, dbo_Machine_Hello.Timestamp1, dbo_Machine_Hello.Quality, dbo_Machine_Hello.[Employee#], dbo_Machine_Hello.Production 
  2. INTO MachineData
  3. FROM dbo_Machine_Hello;
Dec 31 '11 #5

Expert Mod 2.5K+
P: 2,545
Please replace DoCmd.RunSQL with DoCmd.OpenQuery - the RunSQL method would require the SQL itself it turns out, not the name of the query.

OpenQuery will run action queries just as if you were running them from the normal query environment, with the exception that with SetWarnings off you will not have to confirm the replacement of the table.

Taking one of the Case statements as an example, you should end up with:

Expand|Select|Wrap|Line Numbers
  1. Case "World"
  2.    DoCmd.SetWarnings False
  3.    DoCmd.OpenQuery "dbo_Machine_World2"
  4.    DoCmd.SetWarnings True
  5.  
-Stewart
Dec 31 '11 #6

Post your reply

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