Command Object

The Command object allows you to issue commands to the database. These commands include query strings, prepared query strings, and associated parameters. The actual command language and features supported depend on the underlying OLE DB provider.

The Command object can either open a new connection or use an existing connection to perform queries, depending on what is specified in the Command object’s ActiveConnection property:

• If the ActiveConnection property is set with a reference to a Connection object, the Command object uses the existing connection.
• If the ActiveConnection property is specified with a connection string, a new connection is established.

More than one Command object can use the connection from the same Connection object.

Executing commands can generate zero, one, or multiple recordsets. For example, executing a data definition language query does not generate a recordset. Executing a single SELECT statement may generate a recordset, and executing a batch of SELECT statements or a stored procedure may generate more than one recordset.

Execute Method

Use the Execute method of the Command object to execute a query, data definition command, or stored procedure. The syntax is as follows:

Set rs = cmd.Execute(NumRecords, Parameters, Options)

The variable rs is the returned Recordset object, and the three parameters are optional. The NumRecords parameter specifies the number of records returned; Parameters is a variant that specifies initial input parameter values; and Options specifies the type of query (in the form of a CommandTypeEnum constant), if known, to optimize processing.

Command Type Options

Command type options are specified in the CommandType property. A command can be a standard SQL data manipulation language statement, such as SELECT, INSERT, DELETE, or UPDATE, or any data definition language statement, such as CREATE or DROP. A command can also be the name of a stored procedure or table.

The CommandType property has the following values.

If the adCmdStoredProc value is specified, the Command object executes the query string with the call procedure_name ODBC escape function syntax. If adCmdTable is specified, the Command object executes the query string with the SELECT * FROM table_name syntax. If adCmdUnknown is specified, the Command object must perform extra steps to determine the type of query string, thereby degrading performance.

Prepared Property

You can also specify whether to prepare query strings with the Prepared property. Setting the Prepared property allows a query plan to be created at the first execution. The query plan is then used for subsequent executions to enhance performance. A query string should be prepared only when executed more than one time because it may take more time to create a query plan than to execute the query string directly. Performance is enhanced only when you execute the query string the second, third, or nth time.

The Prepared property can also be useful when repeatedly executing a parameterized query string. Different parameter values can be substituted at each execution instead of reconstructing the query string. The Parameter object can be created through the CreateParameter method.