Using the ADO Command Object

In this article, Russell Sinclair continues his discussion of ADO by looking at the Command object–and modifying some data, for a change.

Last month’s article on client/server development by Mary Chipman and Mike Gunderloy should have made clear how important to you the Command object will be (see “Client/Server Development with Access” in the August 1999 issue). In client/server development, the Command object is the workhorse of ADO. It’s the main object through which you can modify bulk data in a database. You use it to run predefined queries or SQL statements, or to create Recordset objects (which I discussed in my August 1999 article, “Everything You Need to Know About ADO Recordsets”). The Command object is the only ADO object that allows you to work with parameters and has its own Parameters collection for that purpose. Knowing how the Command object works solves many problems that can be handled with no other ADO object.

Command properties

Before you do anything with a Command object, you should set its ActiveConnection property. Without providing the Command object with the information that it needs to connect to a data source, you can’t do anything with the object. I always set this property as soon as possible. The ActiveConnection property can be set to one of two things: an existing Connection object, or the same connection string information that you’d use to create a Connection object. These two methods are demonstrated in this code:

The two Command objects in the code will connect to the same place. If you use a connection string, you simply concatenate together the data required to connect to the data source (if you’ve used ODBC connection strings before, the ADO connect string format will look very familiar). If you choose to use an existing Connection object to set this property, since it’s an object, you must use the Set keyword when assigning it to the ActiveConnection property. If you use a string, you don’t use the Set keyword, since all you’re assigning is string data (you’ll generate a runtime error if you do use the Set keyword with a connect string). Once you’ve set the ActiveConnection, you’re free to use the Command object.

While the ActiveConnection property is the first property that I set, the Command object has several others. Table 1 lists all of the properties for the Command object, with a brief description of each.

Table 1. Command properties.

Property Purpose
ActiveConnection The Connection object or string that the Command object uses when executing
CommandText The SQL string or name of the prepared query to execute
CommandTimeout Time to wait after executing a command before the command is canceled and an error is generated
CommandType Specifies how the provider should evaluate the CommandText
Prepared Tells the provider whether or not to keep a compiled version of the SQL statement in memory
State Indicates whether the Command object is open or closed

The CommandText property is where you specify what you want the Command to do. The CommandText can be an existing query/stored procedure in the database, or an SQL statement. For example, if you were going to run a query called qupdChangeData, the code to set this property would be as follows:

It’s a good idea to also set the CommandType property. This property tells the provider what kind of command you’re running. Setting it so that it accurately describes what you’re passing in the CommandText property can speed up the way the provider evaluates the CommandText. Setting the property to adCmdText tells the provider that the Command is a SQL string or provider-specific text. Setting the property to adCmdStoredProc tells the provider that the CommandText refers to a stored procedure or stored query. You’d use adCmdStoredProc to run a query that’s saved in Access using the Jet 4.0 provider. The Jet 3.51 provider isn’t as flexible when working with the Command object as the Jet 4.0 provider. Since the 4.0 provider is included with the latest ADO download at www.microsoft.com/data and works with earlier versions of the Jet databases, you should upgrade to it.

Command methods

The Command object has only three methods. The Execute method runs the Command with the property settings that you’ve made prior to calling the method. The Execute method is the one you’ll use the most and functions almost identically to the Connection object’s Execute method. The last parameter of the Execute accepts the same options that the Connection object’s Execute method does, including the ability to execute your CommandText asynchronously. Like the Connection object, with a CommandText that returns records, the Command object’s Execute method returns a Recordset object:

The first parameter of the Execute method accepts a variable. When you use an update CommandText, that variable will be set to the number of records affected by the Execute:

So why use a Command object, if it’s so much like the Connection object? The main advantage of the Command object over the Connection object is that the Command object allows you to define and provide values for parameters. In fact, if you’re executing a query or stored procedure that requires parameters to be passed to it, you must use the Command object.

As with the Connection object, Command object methods can be run asynchronously. And, like the Connection object, you can also cancel an asynchronous Execute by calling the Command object’s Cancel method. Table 2 lists the methods of the Command object.

Table 2. Command methods.

Method Purpose
Cancel Cancels a pending asynchronous Execute call
CreateParameter Creates a new Parameter object that can be appended to the Parameters collection
Execute Executes a SQL statement or provider-specific command

What about events?

One of the things you might notice if you look at the object model for a Command object is that it doesn’t have any events. Instead, any events that are triggered by calling the Execute method are routed through the Connection object associated with the Command object. You can see a demonstration of this in frmCommandWithEvents in the sample database. By routing events through the Connection object, you manage all Execute calls no matter what object they’re called from.

This code creates a Command object by using a connect string. The code then retrieves the Connection object created by the Command object from the Command object’s ActiveConnection property. Since the object variable used to hold the reference to the Connection has been declared using the WithEvents keyword, event routines can be written using the conn object variable:

Parameters collection

As I mentioned earlier, the Command object has a collection of Parameter objects. This collection allows you to define the type, value, direction, and other properties for the various Parameters in a SQL statement or query. The collection itself has three methods: Append, Delete, and Refresh. The Append and Delete methods allow you to add or remove Parameters from the collection. The Refresh method queries the provider based on the properties you’ve defined for a Command object and fills in the necessary Parameter objects. Of course, there’s a trade-off. While the Refresh method lets you have the data provider build your Parameter collection, it also costs you one more access to the database.

The Parameters collection has only two properties. The Count property returns the number of Parameters in the collection. The Item property returns a reference to a particular Parameter object. Since the Item property is the default property, you don’t need to state it. These two lines of code do exactly the same thing–return the Parameter object “MyParameter” to the prm variable:

Once you’ve retrieved a Parameter object, you can use its methods and properties.

Parameter object

When you’re creating Parameters for a Command object, I’d suggest that you create them in the same order that they’re listed in the query. The reason I recommend this is that the Name property on the Parameter object doesn’t actually do anything. You might think that if your query has a parameter called “City,” and you create a Parameter object with a Name of “City,” ADO would tie them together. You’d be wrong. The Parameter object’s Name property is simply a way for you to refer to the Parameter object in your code. The Refresh method can be very useful here, during the development process, to find out how the Parameters collection is populated. Parameter properties are listed in Table 3.

Table 3. Parameter properties.

Property Purpose
Attributes Sets or returns attributes of data a Parameter can accept
Direction Determines whether the Parameter is an input, output, or return value
Name Name that you can use to refer to the Parameter in code
NumericScale The number of decimal places to which a number will be resolved
Precision The maximum number of digits that are used for a number
Size The size of the data type in bytes
Type The data type of the Parameter
Value The value to use or the value returned

Most of the Parameters that you work with will need only three or four properties set before they can be used. These properties are the Direction, Size, Type, and Value. In Microsoft Access 97, the Direction of a Parameter is always adParamInput. This value indicates an input parameter, since Access doesn’t support output parameters or return values. You really only need to set the Size of a Parameter when you’re dealing with text fields because the Type property implies the Size property for most non-string data types. The Type property can be any of the ADO-accepted data type constants. Table 4 compares the Access data types and the value for the Type property that you should use for ADO. Once you’ve set these important properties, you can set the Value property to the value that you want the parameter to pass to the query.

Table 4. Mapping of Access to ADO data types.

Access data type ADO data type constant
Boolean (Yes/No) AdBoolean
Byte AdUnsignedTinyInt
Currency AdCurrency
Date/Time AdDate
Double AdDouble
Hyperlink AdLongVarChar
Integer AdSmallInt
Long Integer AdInteger
Memo AdLongVarChar
OLE Object AdLongVarBinary
Replication ID AdGUID
Single AdSingle
Text AdVarChar

There are two ways that you can use the CreateParameter methods to add a Parameter to a Command object. One way is to call the CreateParameter method of the Command object to return a Parameter object, so that you can set all of its properties and then append it to the Command’s Parameters collection. Here’s some code that creates a character parameter, eight characters long, with the value “MyValue” and then appends it to the Command object before executing:

Although this is the best method to use if you want to make your code legible to others, you can do everything in one line of code by passing values to the CreateParameter method:

You can also pass parameters as an array in the second parameter of the Command object’s Execute method. While this is more convenient from a coding point of view, your performance will be poorer:

Taking command

Now that you understand how to use a Command object, you can start doing some serious manipulation of data. The objects that you call with the Command object can include stored procedures, queries, or any other precompiled object in the database. As a result, the Command object is the cornerstone of high-performance data access.

 

“In Access 2003 and Access 2007, DAO was again installed as the primary reference for recordset handling breaking the ADO primary reference trend in Access 2000 and 2002”  Garry

 

About Russell Sinclair

Russell Sinclair is a Program Manager at Microsoft working on Microsoft Access. He has over 14 years of experience as an Access developer, is the author of "From Access to SQL Server" and was a Contributing Editor to Smart Access.
This entry was posted in Old Material. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.