Return values ​​from end-to-end query via VBA

Question

I have VBA code to run a query in SQL-Server 2008. It works fine and displays the table 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 to programmatically return these values ​​to VBA code without displaying a table?

Answer 1

The following code has not been verified, but should point 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

Answer 2

All you have to do is execute the query and set its output to the recordset. On top of my head is something like this

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset
Dim cmd as new ADODB.Command

dbCon.ConnectionString="Your Connection String"
with cmd
    .comandtype=adCmdStoredProc
    .commandtext="Your SP name"
    .Parameters.Append .CreateParameter("@Pram1", adVarChar, adParamInput, 50, "WhatEver")
    .ActiveConnection=dbCon
    .NamedParameters = True
    Set rst = .Execute
end with

with rst
    if .EOF=false then
        myVar=!Column1
    end if
end with

rst.close
dbcon.close
set cmd=nothing