Q: DoCmd.RunSQL in 2010 This thread is locked from future replies

Here is an extract of a simple bit of code that is failing me.  I am not certain why.


When I attempt to run this code in ACCESS 2010 I get the following error:


2342: A RunSQL action requires an arguement consisting of an SQL statement.


If I do Debug.Print SQLStr1 & SQLStr2 & SQLStr3 and copy the results from the immediate window into a query, the query runs.

Here is the result of the Debug.Print (just copied and pasted, not cleaned up or anything) from the first record in rs1:


SELECT PolicyNum, ISSUE AS IssueDate, RequestDate, AV_ReqDt, CV_ReqDt, FV0 AS AV_PreValDt, CV0 AS CV_PrevValDt, TAXVTOT0 AS TaxRsv_PrevValDt FROM tblITR_Data LEFT JOIN 201104 AS PrevValDt ON tblITR_Data.PolicyNum = PrevValDt.POLNO WHERE (((PolicyNum)='911673047'));


Can anybody help me spot what I am doing wrong here, please?




Sub GetITR_Data()
On Error GoTo GetITR_Data_Err
Dim db As Database
Dim rs1 As Recordset
Dim SQLStr1 As String
Dim SQLStr2 As String
Dim SQLStr3 As String
Dim tblMth As Long
Dim tblYr As Long
Dim tblName As String
Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblITR_Data")
    Do Until rs1.EOF
        If Month(rs1![RequestDate]) = 1 Then
            tblMth = 12
            tblYr = Year(rs1![RequestDate]) - 1
            tblMth = Month(rs1![RequestDate]) - 1
            tblYr = Year(rs1![RequestDate])
        End If
        tblName = Format(tblYr, "0000") & Format(tblMth, "00")
        SQLStr1 = "SELECT PolicyNum, ISSUE AS IssueDate, RequestDate, AV_ReqDt, CV_ReqDt, FV0 AS AV_PreValDt, CV0 AS CV_PrevValDt, TAXVTOT0 AS TaxRsv_PrevValDt "
        SQLStr2 = "FROM tblITR_Data LEFT JOIN " & tblName & " AS PrevValDt ON tblITR_Data.PolicyNum = PrevValDt.POLNO "
        SQLStr3 = "WHERE (((PolicyNum)='" & rs1![PolicyNum] & "'));"
        DoCmd.RunSQL SQLStr1 & SQLStr2 & SQLStr3
    Exit Sub
    MsgBox Err.Number & ": " & Err.Description
    Resume GetITR_Data_Exit
End Sub



RunSQL is intended to execute the SQL for an action query or a data definition query. You can't use it for a simple SELECT ... FROM ... statement because such a statement does not perform an action but returns records.


From the help for DoCmd.RunSQL:


"... a valid SQL statement for an action query or a data-definition query . It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement."

Kind regards, HansV

Did this solve your problem?

Sorry this didn't help.



What are you attempting to do here with the SQL statement?  If you want to open it as a datasheet at each iteration of the loop, the following function will do this:

Public Function OpenTempQuery(strSQL As String)

    Dim qdf As DAO.QueryDef
    Static n As Integer
    n = n + 1
    ' delete temporary querydef object if exists
    On Error Resume Next
    CurrentDb.QueryDefs.Delete "Temp" & n
    Select Case Err.Number
        Case 0
        ' no error
        Case 3265
        ' temporary querydef does not exist
        ' ignore error
        Case Else
        ' unknown error
        MsgBox Err.Number, vbExclamation, "Error"
    End Select
    ' create temporary querydef object
    Set qdf = CurrentDb.CreateQueryDef("Temp" & n)
    CurrentDb.QueryDefs("Temp" & n).SQL = strSQL
    ' open query and then delete temporary querydef object
    DoCmd.OpenQuery "Temp" & n
    ' delete temporary querydef object
    CurrentDb.QueryDefs.Delete "Temp" & n

End Function

Note that the variable n is declared as Static, which means the function will open multiple datasheets if run more than once without closing the previous datasheet.  They'll be named Temp1, Temp2 etc.

So, having added the function to a standard module, instead of:

DoCmd.RunSQL SQLStr1 & SQLStr2 & SQLStr3

you'd use:

OpenTempQuery SQLStr1 & SQLStr2 & SQLStr3

Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Question Info

Views: 1,258 Last updated: February 11, 2018 Applies to: