Make query works stand alone but not in SQL

[AndyB2]

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:

Private Sub CopyData_Click()
 
Dim dbo_Machine_a As QueryDef
Dim dbo_Machine_Hello As QueryDef
Dim dbo_Machine_World As QueryDef
 
Select Case MachineNameCombo
 
    Case "a"
            DoCmd.SetWarnings False
            DBEngine(0)(0).Execute "dbo_Machine_a"
            DoCmd.SetWarnings True
 
 
    Case "Hello"
            DoCmd.SetWarnings False
            DBEngine(0)(0).Execute "dbo_Machine_Hello"
            DoCmd.SetWarnings True
 
    Case "World"
 
            DoCmd.SetWarnings False
            DBEngine(0)(0).Execute "dbo_Machine_World"
            DoCmd.SetWarnings True
 
End Select
 
End Sub
 

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"

[Stewart Ross]

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:

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:

SetWarnings False
DoCmd.RunSQL "Your Query Name Goes Here"
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

[AndyB2]

Did not work, Here is the code after editing:

 
Private Sub CopyData_Click()
 
Select Case MachineNameCombo
 
    Case "a"
            DoCmd.SetWarnings False
            DoCmd.RunSQL "dbo_Machine_a2"
            DoCmd.SetWarnings True
 
    Case "Hello"
            DoCmd.SetWarnings False
            DoCmd.RunSQL ("dbo_Machine_Hello2")
            DoCmd.SetWarnings True
 
    Case "World"
            DoCmd.SetWarnings False
            DoCmd.RunSQL "dbo_Machine_World2"
            DoCmd.SetWarnings True
 
End Select
 
End Sub
 

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.

[Stewart Ross]

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

[AndyB2]

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:

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 
INTO MachineData
FROM dbo_Machine_Hello;

[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:

Case "World"
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "dbo_Machine_World2"
   DoCmd.SetWarnings True
 

-Stewart