by David Koopman (11 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 14th April 2005
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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
VBcoders
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
No comments have been posted about Open a recordset using ADO with or without parameters. Best practice, very explicit using client si. Why not be the first to post a comment about Open a recordset using ADO with or without parameters. Best practice, very explicit using client si.