Named Commands
We have shown that you can create a Command object, set a command (SQL statements, stored procedures, etc.) to the object's CommandText property, assign an open connection to the object's ActiveConnection property, and then call the Command object's Execute method to have the command executed.
There is another way to execute this command. You can make it a named command, and then call this named command directly on the Connection object (assigned to the ActiveConnection property of the Command object). Naming a command means assigning a name to the Name property of a Command object. For example,
objCmd.Name = "GetCustomers"
objCmd.ActiveConnection = objConn
objConn.GetCustomers objRs
The named command acts as if it were a "custom method" on the Connection object. The result of the command is returned as an out parameter of this "custom method".
The following example illustrates this feature.
'BeginNamedCmd
On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset
' Connect to the data source.
Set objConn = GetNewConnection
objCmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers"
objCmd.CommandType = adCmdText
'Name the command.
objCmd.Name = "GetCustomers"
objCmd.ActiveConnection = objConn
' Execute using Command.Name from the Connection.
objConn.GetCustomers objRs
' Display.
Do While Not objRs.EOF
Debug.Print objRs(0) & vbTab & objRs(1)
objRs.MoveNext
Loop
'clean up
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Exit Sub
ErrHandler:
'clean up
If objRs.State = adStateOpen Then
objRs.Close
End If
If objConn.State = adStateOpen Then
objConn.Close
End If
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'EndNamedCmd