There are many ways to write the code. Here is an example of a function that I setup to do something similar. This example uses output parameters to bring values back. I am picking up the values by ordinal position, but you can do it by name if you give your parameter a unique name which I did not.
If your SP brings back a resultset then just equate the execute to the recordset that you set up. i.e. rst = cmd.Execute
Function GetVariousCounts()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim param4 As Parameter, param5 As Parameter
pubCaseCount = 0
pubInvoiceCount = 0
' Connect
Set cnn = CurrentProject.Connection
' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cnn
cmd.CommandText = "dbo.sp_variousCounts"
cmd.CommandType = adCmdStoredProc
' Set up input parameters.
Set param1 = cmd.CreateParameter("Input", adDouble, adParamInput)
cmd.Parameters.Append param1
param1.Value = pubEmployeeID
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = pubBegDate
Set param3 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param3
param3.Value = pubEndDate
' Set up output parameters.
Set param4 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param4
Set param5 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param5
' Execute command to run stored procedure
cmd.Execute
' ordinal position relative to 0
pubCaseCount = cmd(3)
pubInvoiceCount = cmd(4)
''Debug.Print " pubCaseCount = "; pubCaseCount
End Function
