VB Forum / Database Access / October 2004

Here is a working example, clipped from a real project:

Public Function RetrieveAccessAvailable(ByRef cn As ADODB.Connection, _
                                       ByVal UserName As String, _
                                       ByVal ProjectNumber As String) As Recordset

On Error GoTo ERROR_HANDLER

  Dim rs     As Recordset
  Dim adoCmd As ADODB.Command

  If cn.State <> adStateOpen Then
     cn.Open
  End If

  Set adoCmd = New ADODB.Command

  With adoCmd
      Set .ActiveConnection = cn
     .CommandType = adCmdStoredProc
     .CommandText = "CHAPCAPTURE.CHAPCAPTURE_PKG.RetrieveAccessAvailable"
     .Parameters.Append .CreateParameter("oReturn", adVarChar, adParamOutput, 50) 'return code
     .Parameters.Append .CreateParameter("inUserName", adVarChar, adParamInput, 25, UCase(UserName))
     .Parameters.Append .CreateParameter("inProjectNumber", adVarChar, adParamInput, 25, ProjectNumber)
     Set rs = .Execute
  End With

  Set RetrieveAccessAvailable = rs

EXIT_HANDLER:

  Set rs = Nothing
  Set adoCmd = Nothing
  Exit Function

ERROR_HANDLER:

  goLogger.LogEvent Err.Description & " In DataAccess::RetrieveAccessAvailable"l, Err.Number
  goLogger.LogADOErrors cn
  Resume EXIT_HANDLER

End Function

I hope this helps.