Using Oracle Stored Procedure from Access

[CT Witter]

I'm looking for some help on calling an oracle stored procedure from access. The code below seems to call the sp, but doesn't return any results.
It always seems to have a closed recordset (or more accurately a recordset is never created)

Any thoughts?

Function AccAPILoad(strTransactionID As String, strRegionID As String, strTransactionType As String, _
                      strRequestType As String, strUserID As String, strSubID As String)
    Dim conOracle As New ADODB.Connection
    Dim adoParm As ADODB.Parameter
    Dim adoQuery As ADODB.Command
    Dim strRTransactionID As String
    Dim strRRegionID As String
    Dim strRTransactionType As String
    Dim strRRequestType As String
    Dim strRUserID As String
    Dim strRSubID As String
    Dim strRPersonNumber As String
    Dim strRDetailSvcDate As String
    Dim strRRuleID As String
    Dim strRBaseAmount As String
    Dim strRBaseQty As String
    Dim strRReason As String
    Dim strRIDNumber As String
    Dim strRLineNumber As String
    Dim strRSubLineCode As String
    Dim strRCType As String
    Dim strRErrorString As String
    Dim strRReturnCode As String
    Dim strRResponseString As String
   
    With conOracle
        .ConnectionString = "Provider=MSDAORA;" & _
                          "Data Source= ****;" & _
                          "User ID= *****;" & _
                          "Password=****"
        .ConnectionTimeout = 10
        .CursorLocation = adUseClient
        .Open
    End With
      
    Set adoQuery = New ADODB.Command
    With adoQuery
        Set .ActiveConnection = conOracle
        .CommandType = adCmdStoredProc
        .CommandText = "sp_acool_api"
        '
        'Ins
        Set adoParm = .CreateParameter("pi_s_transaction_id", adVarChar, adParamInput, Len(strTransactionID & ""), strTransactionID)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("pi_s_region_id", adVarChar, adParamInput, Len(strRegionID & ""), strRegionID)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("pi_s_transaction_type", adVarChar, adParamInput, Len(strTransactionType & ""), strTransactionType)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("pi_s_request_type", adVarChar, adParamInput, Len(strRequestType & ""), strRequestType)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("pi_s_user_id", adVarChar, adParamInput, Len(strUserID & ""), strUserID)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("pi_s_sub_id", adVarChar, adParamInput, Len(strSubID & ""), strSubID)
        .Parameters.Append adoParm
        '
        'Outs
        Set adoParm = .CreateParameter("po_s_transaction_id", adVarChar, adParamOutput, 255, strRTransactionID)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_region_id", adVarChar, adParamOutput, 255, strRRegionID)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_transaction_type", adVarChar, adParamOutput, 255, strRTransactionType)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_request_type", adVarChar, adParamOutput, 255, strRRequestType)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_user_id", adVarChar, adParamOutput, 255, strRUserID)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_sub_id", adVarChar, adParamOutput, 255, strRSubID)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_error_string", adVarChar, adParamOutput, 255, strRErrorString)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_return_code", adVarChar, adParamOutput, 255, strRReturnCode)
        .Parameters.Append adoParm
        Set adoParm = .CreateParameter("po_s_response_string", adVarChar, adParamOutput, 255, strRResponseString)
        .Parameters.Append adoParm
        
    
       ' .Execute
       Dim rst As ADODB.RecordSet
             Set rst = .Execute
      ' Process results from recordset, then close it.
      'http://support.microsoft.com/default.aspx?scid=kb;en-us;194792
          Dim i As Integer
          Dim rStr As String
          
          Do While (Not rst Is Nothing)
          If rst.State = adStateClosed Then Exit Do
          While Not rst.EOF
              For i = 0 To rst.Fields.Count - 1
                  rStr = rStr & " : " & rst(i)
              Next i
              Debug.Print Mid(rStr, 3, Len(rStr))
              rst.MoveNext
              rStr = ""
          Wend
          Debug.Print "----------------------"
          Set rst = rst.NextRecordset
          Loop
          
            rst.Close
            Set rst = Nothing
        Set .ActiveConnection = Nothing
   
    End With
    Set adoQuery = Nothing
   
   End Function