Creating and Executing a Simple Command

A simple command is one that is not parameterized and requires no persistence. There are three ways to create and execute a simple command.

• Using a Command object
• Using a Connection object
• Using a Recordset object

Using a Command object

To create a simple command using a Command object, you must assign the instruction to the CommandText property of a Command object and set the appropriate value for the CommandType property. Executing the command requires that an open connection is assigned to the ActiveConnection property of the Command object, followed by a call to the Execute method on the Command object.

The following code snippet shows the basic method of using the Command object to execute a command against a data source. This example uses a row-returning command, and returns the results of the command execution as a Recordset object.

    'BeginBasicCmd
    On Error GoTo ErrHandler:
    
    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRs As New 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 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
'EndBasicCmd


'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
    Dim oCn As New ADODB.Connection
    Dim sCnStr As String
    
    sCnStr = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _
             "Integrated Security='SSPI';Initial Catalog='Northwind';"
    oCn.Open sCnStr
    
    If oCn.State = adStateOpen Then
        Set GetNewConnection = oCn
    End If
    
End Function
'EndNewConnection

Using a Recordset object

You can also create a command as a text string and pas it to the Open method on a Recordset object, together with the command type (adCmdText), for execution. The following code snippet demonstrate this.

    
    Const DS = "MySqlServer"
    Const DB = "Northwind"
    Const DP = "SQLOLEDB"
    
    Dim objRs As New ADODB.Recordset
    Dim CommandText As String
    Dim ConnctionString As String
    
    CommandText = "SELECT OrderID, OrderDate, " & _
                         "RequiredDate, ShippedDate " & _
                         "FROM Orders " & _
                         "WHERE CustomerID = 'ALFKI' " & _
                         "ORDER BY OrderID"
    ConnectionString = "Provider=" & DP & _
                       ";Data Source=" & DS & _
                       ";Initial Catalog=" & DB & _
                       ";Integrated Security=SSPI;"
    
    ' Connect to data source and execute the SQL command.
    objRs.Open CommandText, ConnectionString, _
                adOpenStatic, adLockReadOnly, adCmdText
    
    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
    Set objRs = Nothing

Using a Connection object

You can also execute a command on an open Connection object. The previous code example now becomes this:

    Const DS = "MySqlServer"
    Const DB = "Northwind"
    Const DP = "SQLOLEDB"
    
    
    Dim objConn As New ADODB.Connection
    Dim objRs As New ADODB.Recordset
    
    CommandText = "SELECT OrderID, OrderDate, " & _
                         "RequiredDate, ShippedDate " & _
                         "FROM Orders " & _
                         "WHERE CustomerID = 'ALFKI' " & _
                         "ORDER BY OrderID"
                         
    ConnectionString = "Provider=" & DP & _
                       ";Data Source=" & DS & _
                       ";Initial Catalog=" & DB & _
                       ";Integrated Security=SSPI;"
    
    ' Connect to the data source.
    objConn.Open ConnectionString
    
    ' Execute command through the connection and display...
    Set objRs = objConn.Execute(CommandText)
    
    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 objRs = Nothing
    Set objConn = Nothing