Step 3: Execute the Command (ADO Tutorial)

You are Here...

Make a connection to a data source.
Optionally, create an object to represent an SQL query command.
Optionally, specify values in the SQL command as variable parameters.
Execute the command. If the command is row-returning, store the rows in a storage object.
Optionally, navigate, examine, manipulate, and edit the data.
If appropriate, update the data source with changes from the storage object. Optionally, embed the update in a transaction.
If a transaction was used, accept or reject the changes made during the transaction. End the transaction.

Discussion

The three methods that return a Recordset are Connection.Execute, Command.Execute, and Recordset.Open. This is their syntax in Visual Basic:

connection.Execute(CommandText, RecordsAffected, Options)
command.Execute(RecordsAffected, Parameters, Options)
recordset.Open Source, ActiveConnection, CursorType, LockType, Options

These methods are optimized to take advantage of the strengths of their particular objects.

Before you issue a command, you must implicitly or explicitly open a connection. Each method that issues a command represents the connection differently:

The Connection.Execute method uses the connection embodied by the Connection object itself.
The Command.Execute method uses the Connection object set in its ActiveConnection property.
The Recordset.Open method uses a connection string, its ActiveConnection parameter, or the Connection object set in its ActiveConnection property.

Another difference is the way the command is specified in the three methods:

In the Connection.Execute method, the command is command text.
In the Command.Execute method, the command is not visible—it is specified in the Command.CommandText property. Furthermore, the command can be parameterized.
In the Recordset.Open method, the command is the Source argument, which can be command text or a Command object. (The Source argument can also be a string specifying a table name, a stored procedure, or the file name of a persisted Recordset.)

Each method trades off functionality versus performance:

The Execute methods are intended for, but not limited to, executing commands that don't return data.
Both Execute methods return read-only, forward-only Recordset objects.
The Command.Execute method allows you to use parameterized commands that can be reused efficiently.
The Open method allows you to specify the CursorType (strategy and object used to access the data); and LockType (specify the degree of isolation from other users, and whether the cursor should support updates in immediate or batch modes (discussed in greater detail later)).

Study these options; they embody much of the functionality of a Recordset. Of particular importance is the decision to use the Microsoft Cursor Service for OLE DB. Please refer to Microsoft Cursor Service for OLE DB for details about the implications of this decision.

This tutorial will make changes to the Recordset in batch mode; therefore, a LockType of adLockBatchOptimistic is specified. Batch processing requires the Cursor Service, so the CursorLocation property is set to adUseClient. Since the Command object is already set to an open connection, the ActiveConnection parameter cannot be specified in the Open method.

The Recordset is declared and used like this:

Dim rs As New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockBatchOptimistic

Next Step 4: Manipulate the Data