DoCmd.RunSQL in 2010

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?

 

Thanks!

 

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")
'
    rs1.MoveFirst
    Do Until rs1.EOF
        If Month(rs1![RequestDate]) = 1 Then
            tblMth = 12
            tblYr = Year(rs1![RequestDate]) - 1
        Else
            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
        rs1.MoveNext
    Loop
'
GetITR_Data_Exit:
    Exit Sub
'
GetITR_Data_Err:
    MsgBox Err.Number & ": " & Err.Description
    Resume GetITR_Data_Exit
End Sub

 

Question Info


Last updated February 11, 2018 Views 1,292 Applies to:
Answer

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
www.eileenslounge.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
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.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.