Search code examples
sqlms-accessvbams-access-2007wizard

Used Access 2007 query wizard to create SQL, still will not run in VBA

I'm trying to write VBA in access 2007 that goes through one table and grabs all the records with the appropriate testcode and adds them to a table specifically for only that tescode. The first sql string is supposed to go through the code-specific table to find the latest date that was entered. The second sql string is supposed to find the one entered at the latest time on that date. And the third is supposed to take that date and time and grab all the records from the general table for that testcode that were entered since the last update and move them to the code-specific table.

I actually used the query wizard to generate the first two sql statements, yet i keep getting the error:

A RunSQL action requires an argument consisting of an SQL statement.

The only thing I changed after I copied and pasted the SQL statement was that I added the proper quotation marks and stuff, so I am not sure why it won't work. It also means I can't check the later code, since I can't get through the first part.

My question is, how can it be wrong if I copied and pasted it from the wizard? Below is the section of my code. I was using the messageboxes to read through and \check the SQL statements for problems. 

dateSQL = "SELECT Max(tbl505.TESTDATE) AS MaxDATE " & _
    "FROM tbl505 " & _
    "HAVING (([tbl505].[Plant]='Freeport'));"

MsgBox (dateSQL)
DoCmd.RunSQL dateSQL

timeSQL = "SELECT Max(tbl505.TESTTIME) AS MaxTIME " & _
    "FROM tbl505 " & _
    "HAVING (([tbl505].[Plant]='Freeport')) AND (([tbl505].[TESTDATE]= #" & MaxDATE & "#;"

MsgBox (timeSQL)
DoCmd.RunSQL timeSQL


 strSQL = "INSERT INTO tbl505 (ORDERNO,CODE,BATCHNO,TESTNO,TESTDATE,PRODDATE,TESTTIME) " & _
    "SELECT ORDERNO,CODE,BATCHNO,TESTNO,TESTDATE,PRODDATE,TESTTIME " & _
    "FROM DSResultFRP " & _
    "WHERE DSResultFRP.[TESTDATE] >= #" & MaxDATE & "# AND DSResultFRP.[TESTDATE] > #" & MaxTIME & "# AND DSResultFRP.[TESTCODE] = '505'"

DoCmd.RunSQL strSQL
Solution

  • DoCmd.RunSQL is only for action-type queries (INSERT, UPDATE, DELETE). In the context you are using it for your first two queries, it doesn't make sense to the command. If you want to use the values returned in those queries, look into a DAO/ADO recordset solution, or a DMax function solution.