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