Named Commands

You can set the Name property on a Command object and then execute the command by calling it as if it were a method on the Command object ActiveConnection property. This is illustrated in the following example, in which the command is named GetCustomers. Notice that the code passes in a declared and instantiated Recordset object to the GetCustomers "method." You can also pass in parameters to the "method" if they are required by the Command.

'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