Pass-through query with output parameter

Question

I am converting an adp to an accdb. This adp was executing a stored procedure with one input and one output parameters using the following code just fine:

Public Function GetTest(strID As String) As Integer

    Dim cmd As ADODB.Command

    Dim par As ADODB.Parameter

    Set cmd = New ADODB.Command

    cmd.ActiveConnection = CurrentProject.Connection

    cmd.CommandText = "usp_Test"

    cmd.CommandType = adCmdStoredProc

    'Define the input and output variable and append them to the collection

    Set par = cmd.CreateParameter("@id", adVarChar, adParamInput, 9)

    cmd.Parameters.Append par

    Set par = cmd.CreateParameter("@Test", adInteger, adParamOutput)

    cmd.Parameters.Append par

    'assign the value for the input variable to be passed to SQL Server

    cmd.Parameters("@id") = strID

    cmd.Execute

    'assign the stored procedure return values to the access variables

    GetTest = cmd.Parameters("@Test").Value

    Set cmd = Nothing

End Function

However, it now generates the following run-time error when trying to execute this from the accdb: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

I then decided to try this using a pass-through query. I have been using pass-through queries to execute stored procedures in the accdb, but this is the first time I'm trying to execute a stored procedure that has an output parameter. I have been working on the following code, but it generates the following error: "Procedure or function 'usp_Test' expects parameter '@Test', which was not supplied." I did supply it though in the SQL.

Public Function GetTest(strID As String) As Integer

    Dim rs As ADODB.Recordset

    Dim conn As ADODB.Connection

    CurrentDb.QueryDefs("PTQ").SQL = "exec usp_Test '" & strID & "'"

    Set conn = New ADODB.Connection

    conn.ConnectionString = Replace(CurrentDb.QueryDefs("PTQ").Connect, "ODBC;", vbNullString)

    conn.Open

    Set rs = New ADODB.Recordset

    rs.Open CurrentDb.QueryDefs("PTQ").SQL, conn

    Set rs = rs.NextRecordset

    GetTest = rs.Fields(0).Value

    rs.Close

    Set rs = Nothing

    conn.Close

    Set conn = Nothing

End Function

What do I need to do to get this stored procedure with output parameter to work? Any help is appreciated.

Answer 1

I found a way to get the functionality I need, i.e. have a value returned from the stored procedure,  without using an output parameter on it:

I removed the output parameter and returned the value using the regular result set instead (from the SELECT statement). I have a pass-through query in access that executes this sp and is the rowsource for a combobox. I then obtain the value I'm interested in by reading the first item of the combobox.