DoCmd.RunSQL in 2010

Question

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

Answer 1

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

Answer 2

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

Answer 3

Thank you, that will do what I am trying to do!

Answer 4

Thanks! 

I think I even knew that at some point, but frustration and tight deadlines make me forget details like that.