Creating and Executing a Simple Command

Though not a typical usage of the Command object, the following code shows the basic method of using the Command object to execute a command against a data source. In this case, it is a row-returning command, so it returns the results of the command execution into a Recordset object.

'BeginBasicCmd
    Dim objConn As ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRs As ADODB.Recordset
    
    objCmd.CommandText = "SELECT OrderID, OrderDate, " & _
                         "RequiredDate, ShippedDate " & _
                         "FROM Orders " & _
                         "WHERE CustomerID = 'ALFKI' " & _
                         "ORDER BY OrderID"
    objCmd.CommandType = adCmdText
    
    ' Connect to the data source.
    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn
    
    ' Execute once and display...
    Set objRs = objCmd.Execute
    
    Debug.Print "ALFKI"
    Do While Not objRs.EOF
        Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
                    objRs(2) & vbTab & objRs(3)
        objRs.MoveNext
    Loop
    
    'clean up
    objRs.Close
    objConn.Close
    Set objConn = Nothing
    Set objCmd = Nothing
'EndBasicCmd

The command to be executed is specified with the CommandText property.

Note   Several examples in this section call a utility function, GetNewConnection, to establish a connection with the data provider. To avoid redundancy, it is listed only once, here:

'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
    Dim oCn As New ADODB.Connection
    Dim sCnStr As String
    
    sCnStr = "Provider=SQLOLEDB;Data Source=MySrvr;" & _
             "User Id=MyId;Password=123aBc;Database=Northwind;"
    oCn.Open sCnStr
    
    Set GetNewConnection = oCn
End Function
'EndNewConnection