Return values from a pass-through query via VBA

Question

I have VBA code to run a query in SQL-Server 2008. It runs fine and displays the table that I need. The code that does this is here:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyStoredProcedure")

qdf.SQL = "exec [WCNS_Ops].[dbo].MyStoredProcedure [plus a bunch of parameters]"   
DoCmd.OpenQuery "MyStoredProcedure"

which displays this table:

My question is this: How do I programmatically return these values to VBA code without displaying the table?

Answer 1

The following code is untested, but should get you pointed in the right direction:

Set db = CurrentDb

Set qdf = db.QueryDefs("MyStoredProcedure")
qdf.ReturnsRecords = True
qdf.SQL = "exec [WCNS_Ops].[dbo].MyStoredProcedure [plus a bunch of parameters]"  

With qdf.OpenRecordset(dbOpenSnapshot)  'could also be dbOpenDynaset, etc. '
    Do Until .EOF
        Debug.Print !firstid
        Debug.Print !lastid
        .MoveNext
    Loop
End With