Using the Command Object

The ADO Command object accesses and builds queries that are executed against a data source. You can also use Command objects to access stored procedures in an external database.

Using ADO, you don't have to create a Connection object before using the Command object. However, if you do not associate the new Command object with an active connection, a new implicit Connection object will be created automatically, using additional server resources.

Command Object Properties

The following table describes commonly used properties of the Command object. For a complete listing of Command object properties, read the article "Command Object (ADO)" in ADO Help.

Property	Description
ActiveConnection	Sets or returns the active connection used by the object.
CommandText	SQL command, stored procedure name, or table name that will be used by the object.
CommandType	Indicates if the CommandText property is an SQL command, stored procedure, or a table name.
Prepared	Indicates whether the SQL command should be created as a temporary stored procedure.
State	Indicates whether the command is currently opened, closed, or executing.

If you do not use an active connection when you create the Command object, you must pass the required connection string to the ActiveConnection property.

Command Object Methods

The following table describes methods of the Command object.

Method	Description
Cancel	Cancels the currently executing command.
CreateParameter	Creates a parameter object (for use with stored procedures).
Execute	Executes a SQL command.

Creating a Command from a Connection

Although an existing active connection is not required, it is more efficient to create Command objects from established connections. Once the Command object has been created, it can be used to execute the specified command or build a recordset.

The following example code uses a Connection object and a Command object to increase the book price for all records in the Books table by 10 percent:

Dim comPriceUpdate As Command
Set comPriceUpdate = New Command
With comPriceUpdate
    ' An existing Connection Object is referenced
    .ActiveConnection = cnStateUBookstore
    .CommandText = "UPDATE Books SET Price = Price * 1.1"
  
    ' call the Execute method to update the prices
    .Execute
End With

Creating a Stand-Alone Command Object

Since ADO provides a flat object model, you do not have to explicitly create a Connection object. Instead, you can pass the required connection information to the ActiveConnection property of the Command object. Then, when you use the Execute method to run the SQL command, a connection is established for you. However, using this technique, you cannot access the Connection object from your Visual Basic code.

The following example code uses a Command object to increase the book price for all records in the Books table by 10 percent:

Dim comPriceUpdate As Command
Set comPriceUpdate = New Command
With comPriceUpdate
    ' No connection object is used
    .ActiveConnection = "Provider=SQLOLEDB;" & _
                        "User ID=sa;" & _
                        "Data Source=MSERIES1;" & _
                        "Initial Catalog=StateUBookstore"
    .CommandText = "UPDATE Books SET Price = Price * 1.1"

    ' call the Execute method to update the prices
    .Execute
End With