OK, not sure if there is a way around this. I have some relatively complicated SQL code in a stored proc. The proc is working fine. When I execute this code in SSMS, it returns a record set and an output parameter (varchar(50):
So, I need to access this data from Microsoft Access. I need the proc to return a recordset that I can work with and I also need the output parameter value. However, when I do this in VBA:
The msgbox displays an empty string. The problem I know is the line that says:
If I change it to just:
it returns the right value for the output parameter. However, I also need to work with the recordset returned by the proc, which is why I was setting the myrst object reference to mycmd.execute. Is there any way to get both the data in the recordset returned by the stored proc into an ADODB Recordset AND the output parameter's value into a string variable (besides executing the proc twice)?
Code:
Declare @name varchar(50) exec pra_StoredProc @id=12, @name=@name OUTPUT select @name
Code:
Dim myrst As New ADODB.Recordset Dim mycmd As New ADODB.Command Dim mycon As New ADODB.Connection mycon.open connectionString With mycmd .ActiveConnection = mycon .CommandText = "pra_StoredProc" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@id", adInteger, adParamInput, , 12) .Parameters.Append .CreateParameter("@name", adVarChar, adParamOutput, 50) Set myrst = .Execute MsgBox .Parameters("@name") End With
Code:
Set myrst = .Execute
Code:
.Execute
Comment