• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Open a recordset using ADO with or without parameters. Best practice, very explicit using client si

Open a recordset using ADO with or without parameters. Best practice, very explicit using client si

Open a recordset using ADO with or without parameters. Best practice, very explicit using client side cursor.

API Declarations


Private CMD As ADODB.Command
Private PRM As ADODB.Parameter
Private RS As ADODB.Recordset



Rate Open a recordset using ADO with or without parameters. Best practice, very explicit using client si

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Public Function functionName(ByVal intID as Integer, ByVal strSSN As String) as ADODB.Recordset 

   Set CN = New ADODB.Connection
        CN.CursorLocation = adUseClient
        CN.ConnectionString = "File Name=" & App.Path & "\NRUA.udl" 
        CN.ConnectionTimeout = 0
        CN.Open

    Set CMD = New ADODB.Command
    Set CMD.ActiveConnection = CN
        CMD.CommandTimeout = 0
        CMD.CommandType = adCmdStoredProc
        CMD.CommandText = "USP_YourStoredProcedure"
    
    Set PRM = New ADODB.Parameter
    Set PRM = CMD.CreateParameter("ID", adInteger, adParamInput, , strIn)
        CMD.Parameters.Append PRM
    Set PRM = CMD.CreateParameter("SSN", adVarChar, adParamInput, 11, strSSN)
        CMD.Parameters.Append PRM

    Set RS = New ADODB.Recordset
    Set RS.Source = CMD
        RS.CursorType = adOpenStatic
        RS.LockType = adLockOptimistic
        RS.Open

    Set functionName = RS

    CN.Close
    Set PRM = Nothing
    Set CMD = Nothing
    Set CN = Nothing
End Function

'Without Parameter
Public Function functionName() as ADODB.Recordset 

   Set CN = New ADODB.Connection
        CN.CursorLocation = adUseClient
        CN.ConnectionString = "File Name=" & App.Path & "\NRUA.udl" 
        CN.ConnectionTimeout = 0
        CN.Open

    Set CMD = New ADODB.Command
    Set CMD.ActiveConnection = CN
        CMD.CommandTimeout = 0
        CMD.CommandType = adCmdStoredProc
        CMD.CommandText = "USP_YourStoredProcedure"

    Set RS = New ADODB.Recordset
    Set RS.Source = CMD
        RS.CursorType = adOpenStatic
        RS.LockType = adLockOptimistic
        RS.Open

    Set functionName = RS

    CN.Close
    Set CMD = Nothing
    Set CN = Nothing

End Function