Using the Command Object
An application can use the Command object 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 ActiveConnection property of the Command object:
- 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 set 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 one SELECT statement can generate a recordset, and executing a batch of SELECT statements or a stored procedure can 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:
Set rs = cmd.Execute(NumRecords, Parameters, Options)
The variable rs is the returned Recordset object, and the parameters are optional. The NumRecords parameter specifies the number of rows 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, UPDATE, or DELETE, 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.
| CommandTypeEnum Constant | Query String |
|---|---|
| adCmdFile | File name of a persistently stored Recordset object |
| adCmdStoreProc | Stored procedure |
| adCmdTable | Table name |
| adCmdTableDirect | Table name whose columns are all returned |
| adCmdText | SQL statement |
| adCmdUnknown | Contents of the command are not known (default) |
| adCmdUnspecified | Unspecified command type argument |
Prepared Property
You can prepare query strings using the Prepared property. Setting the Prepared property allows a query plan to be created when it is first executed. 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 more than one time.
The Prepared property can also be useful when executing a parameterized query string repeatedly. Different parameter values can be substituted each time it is executed instead of reconstructing the query string. The Parameter object can be created using the CreateParameter method.