Re: HOW TO: ADO 2.5 Oracle Stored Proc into Recordset

Check out my article about a utility that does this for SQL Server, but could easily be adapted to Oracle.

http://www.swynk.com/friends/Lesandrini/SP2VB01.asp

Below, I've pasted the syntax which my utility outputs. (watch for word wrap)

--
Danny J. Lesandrini
http://datafastdownloads.cjb.net
http://www.swynk.com/lesandrini


Option Explicit

Private lngRecordsAffected As Long
Private strSQL7Error As String

Property Get RecordsAffected() As String
    RecordsAffected = lngRecordsAffected
End Property

Property Get SQLError() As String
    SQLError = strSQL7Error
End Property



'//////////// Code for:  ems_GetNextCounters ////////////
'  Created on 10/6/2000
'  Created by Danny Lesandrini
'  Created for Dean Evans and Associates, Inc.
'
'  Parameters expected by this Stored Proc
'  ----------------------------------------
'    @TableName varchar(75)
'    @HowMany int
'    @NextCounter int OUTPUT
'  ----------------------------------------


Public Function Exec_ems_GetNextCounters( _
    ByVal strConnect As String, _
    ByVal TableName As String, _
    ByVal HowMany As Long, _
    ByVal  As Long ) As ADODB.Recordset

On Error Resume Next

Dim cnn As New ADODB.Connection
Dim com As New ADODB.Command

    cnn.Open strConnect
    Set com.ActiveConnection = cnn

    With com



        .CommandText = "ems_GetNextCounters"

        .CommandType = adCmdStoredProc




        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, , 0)

        .Parameters.Append .CreateParameter("@TableName", adVarChar, adParamInput, 75, TableName)

        .Parameters.Append .CreateParameter("@HowMany", adInteger, adParamInput, 8, HowMany)

        .Parameters.Append .CreateParameter("@NextCounter", adInteger, adParamOutput)


        Set Exec_ems_GetNextCounters = .Execute(lngRecordsAffected)
        strSQL7Error = Err.Description

    End With

    Set com.ActiveConnection = Nothing
    Set cnn = Nothing

End Function