Named Commands

Creating and Executing a Simple Command shows one way to execute a command. There is another way: 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

See Also

Connection Object (ADO)