Step 3: Execute the Command (ADO Tutorial)

See Also   

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 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 specifies either a connect string or Connection object operand, or uses 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 a string.
  
  
• In the Command.Execute method, the command isn't visible—it's specified in the Command.CommandText property. Furthermore, the command can contain parameter symbols (‘?’) which will be replaced by the corresponding parameter in the Parameters VARIANT array argument.
  
  
• In the Recordset.Open method, the command is the Source argument, which can be a string or a Command object.

Each method trades off functionality versus performance:

• The Execute methods are intended for—but are not limited to—executing commands that don't return data.
  
  
• Both Execute methods return fast but read-only, forward-only Recordset objects.
  
  
• The Command.Execute method allows you to use parameterized commands that can be reused efficiently.
  
  
• On the other hand, 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).
  
  
• We advise you to study these options; they embody much of the functionality of a Recordset. 

This tutorial uses a dynamic cursor to batch any changes to the Recordset. For this reason, use the following:

Recordset rs = New ADODB.Recordset
rs.Open cmd, conn, adOpenDymanic, adLockBatchOptimistic

Next   Step 4