Assign Recordset to Variable in VBA

Question

I am trying to assign variables value from recordset and insert values into an Access table. I also need to clear the table and insert new set of data before inserting. The recordset is from a stored procedure in SQL Server. Following does not seem to work:

Dim conn As ADODB.Connection, cmd As ADODB.Command, rst As 
ADODB.Recordset
Dim Itm As String, JobNo As Integer, RevNo As Integer, DUStatus As Date, LDUStatus As Date, UTrigger As String


Set conn = New ADODB.Connection
conn.ConnectionString = "Provider='sqloledb';Data Source=SERVER;Initial Catalog='Database';Integrated Security='SSPI';"
conn.Open


Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = conn
    .CommandText = "rg_ItemsQuerySP"
    .CommandType = adCmdStoredProc

    .Parameters.Append .CreateParameter("@JobNo", adInteger, adParamInput, , TempJobNo)
    .Parameters.Append .CreateParameter("@RevNo", adInteger, adParamInput, , TempRevNo)
End With

Set rst = cmd.Execute

If rst.EOF Then Exit Function
rst.MoveLast
rst.MoveFirst
    With rst
      Do While Not .EOF

            Itm = rst.Fields("Item")
            JobNo = rst.Fields("Job No")
            RevNo = rst.Fields("Revision No")
            DUStatus = rst.Fields("DateUpdatedStatus")
            LDUStatus = rst.Fields("LastDateUpdatedStatus")
            UTrigger = rst.Fields("UpdateTrigger")

        DoCmd.RunSQL ("INSERT INTO ItemsQuerySP_Temp values " & Itm & ", " & JobNo & ", " & RevNo & ", " & DUStatus & ", " & LDUStatus & ", " & UTrigger & ";")
        rst.MoveNext
    Loop
    End With

   conn.Close
   Set conn = Nothing

What is your question? You post code without telling us the issues or problems you face. — Parfait
– Parfait, Commented Aug 27, 2019 at 21:48
Unless all your values are numeric, some of them will need quotes. And I think you also need parentheses around the list of values. — Tim Williams
– Tim Williams, Commented Aug 27, 2019 at 21:53

Parfait · Accepted Answer · 2019-08-28 22:25:58Z

1

Possibly your issue is the lack of quotes around string variables which would raise SQL error. Since you use ADO parameters, continue to use parameters via QueryDef, avoiding string concatenation (i.e., ampersands) or punctuation (i.e., quotes):

SQL (save below as an MS Access saved query, adjust types as needed: Text, Long, Double, etc.)

PARAMETERS PrmItm Text, PrmJobNo Text, PrmRevNo Text, 
           PrmDUStatus Text, PrmLDUStatus Text, PrmUTrigger Text;
INSERT INTO ItemsQuerySP_Temp 
VALUES(PrmItm, PrmJobNo, PrmRevNo, 
       PrmDUStatus, PrmLDUStatus, PrmUTrigger)

VBA (relevant section)

Dim qdef AS QueryDef

' ... same as above...
Set qdef = CurrentDb.QueryDefs("mySavedQuery") 

With rst
   Do While Not .EOF

        ' BIND PARAMETERS
        qdef!PrmItm = rst.Fields("Item")
        qdef!PrmJobNo = rst.Fields("Job No")
        qdef!PrmRevNo = rst.Fields("Revision No")
        qdef!PrmDUStatus= rst.Fields("DateUpdatedStatus")
        qdef!PrmLDUStatus = rst.Fields("LastDateUpdatedStatus")
        qdef!PrmUTrigger = rst.Fields("UpdateTrigger")

        ' EXECUTE ACTION
        qdef.Execute dbFailOnError
        .MoveNext
   Loop
End With

Set qdef = Nothing

edited Aug 28, 2019 at 22:25

answered Aug 27, 2019 at 21:57

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CMW Over a year ago

It seems to be working but it goes into an infinite loop while inserting data to the ItemsQuerySP_Temp table. It is inserting same record infinitely. What could have gone wrong?

Parfait Over a year ago

You have to incorporate Recordset.MoveNext. See edit. I hadn't realized I left it out.

CMW Over a year ago

Works great now! Thanks!

Collectives™ on Stack Overflow

Assign Recordset to Variable in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related