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?
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
All you need to do is execute the query and set its output to a recordset. Off the top of my head 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
