The Command Object

< Day Day Up >

The Command object contains information about the task that the database will run. Depending on the type of command you want to execute, you might also have to use the Parameter object and the Parameters collection to execute a stored procedure.

Key Properties and Methods of the Command Object

Table 23-5 lists some of the key properties and methods associated with the Command object.

Table 23-5: Key Properties and Methods of the *Command* Object
Property/Method	Description
ActiveConnection	Property: points to an open Connection object that will be used to execute the command or that contains a connection string that will be used to connect to the database.
CommandText	Property: the command that will be executed on the database.
CommandType	Property: specifies the type of command in CommandText. Can be adCmdText, adCmdTable, or adCmdStoredProc.
CreateParameter(Name, Type, Direction, Size, Value) As Parameter	Method: creates a Parameter object using the specified type information.
Execute(RecordsAffected, Parameters, Options) As Recordset	Method: executes the command and optionally returns a Recordset object containing any rows retrieved from the database.
Name	Property: contains the name of the command.
Parameters	Property: pointer to a Parameters collection object containing the parameter values that will be substituted into the command when it’s executed.

The CommandText property contains the command that you want to execute, whereas the CommandType property describes the type of command stored in the CommandText property. There are three basic types of commands you can create: SQL statements, table names, and stored procedures.

For more information on SQL syntax, see Chapter 22, “Excel and the Structured Query Language”.

The ActiveConnection property contains either an object reference to an open Connection object, or it contains a connection string that will be used to dynamically create a connection to the database when the Execute method is called.

The CreateParameter method creates a new Parameter object using the specified information about the data type. Once you’ve created the new Parameter object, you must add it to the Parameters collection using the Parameters.Append method.

Inside Out
Stored Procedures

Stored procedures are merely precompiled routines available on the database server that someone can execute to perform a database task. Although Access doesn’t support stored procedures, many other database systems, such as SQL Server, Oracle, and DB2, all support stored procedures.

Typically, stored procedures are written using SQL statements connected together with other statements, such as If statements, looping statements, Print statements, and so on. Each vendor has its own syntax for these statements, so stored procedures aren’t portable from one database system to another. However, stored procedures are much faster than simply executing an SQL statement. When stored procedures are created on the database server, they are stored in a precompiled form, which saves a lot of resources because the SQL statements need not be compiled each time you execute a database command.

Stored procedures are in many ways like subroutines. You can create stored procedures with a list of parameters and pass values for each parameter when you run the command. Again, this has a big impact on speed and throughput, both of which are a big concern to most database administrators. Finally, stored procedures offer another way to secure access to the database. In these days when computer hackers commonly attack all kinds of computers, many people consider security even more important than performance.

Using the Parameters Collection

The Parameters collection contains information about the parameters associated with a Command object. (See Table 23-6.)

Table 23-6: Properties and Methods of the *Parameters* Collection
Property/Method	Description
Append(Parameter)	Method: appends the specified Parameter object to the collection.
Count	Property: returns the number of items in the collection.
Delete(index)	Method: removes the Parameter object with the specified index.
Item(index)	Property: returns the Parameter object at the location specified by index.
Refresh	Method: connects to the database and retrieves a copy of the parameter information for the stored procedure specified in CommandText.

The Parameters collection is essentially a normal collection object with two differences. The Append method is used to add a new Parameter object to the end of the collection. Depending on how the parameters are defined in the command, the order can be very important.

The Refresh method populates the Parameters collection using the name of the stored procedure from the CommandText property. This can be a useful shortcut that avoids the extra code to define all the parameters manually.

Note

Although you can use the Refresh method to get a copy of the parameters for a stored procedure directly from the database, you might not want to do this each time you call the stored procedure. There’s a fair amount of overhead associated with retrieving the parameters from the database, which could add up if you’re calling the stored procedure from within a loop.

Using the Parameter Object

The Parameter object contains a number of properties that describe a specific parameter that is passed to a parameterized query or a stored procedure. (See Table 23-7.)

Table 23-7: Key Properties of the *Parameter* Object
Property	Description
Direction	Indicates whether the parameter is an input (adParamInput), output (adParamOutput), or input/output (adParamInputOutput) parameter to the stored procedure.
Name	Contains the name of the parameter.
NumericScale	Contains the number of digits to the right of the decimal point for a numeric field.
Precision	Contains the total number of digits in a numeric field.
Type	Contains the data type associated with the parameter. Some common values are adSmallInt, adInteger, asSingle, adDouble, adCurrency, adDate, adBSTR, adBoolean, adDecimal, adBigInt, adBinary, adChar, adWChar, adNumeric, adDBDate, adDBTime, adVarNumeric, adVarChar, adLongVarChar, adVarWChar, adLongVarWChar, adVarBinary, and asLongVarBinary.
Value	Contains the value of the parameter. For input parameters and input/output parameters, this value will be passed to the stored procedure. For input/output and output parameters, this value is set after the stored procedure is executed.

Each Parameter object describes a single parameter to a stored procedure. The Name property must match the parameter name defined in the stored procedure. You must specify the database type associated with the parameter, along with which direction the value is passed.

The Value property contains the value that’s passed and/or returned from the stored procedure. Typically, you define a Command object with all its associated parameters only once. Then you modify the set of Value properties so that you pass the appropriate information to the stored procedure.

< Day Day Up >