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:

'set connection with a Connection object

conn.Provider = "Microsoft.Jet.OLEDB.4.0"

conn.Properties("Data Source") = "MyDatabase.mdb"

conn.Open

Set cmd1 = New ADODB.Command

Set cmd1.ActiveConnection = conn



'set connection with connect information

Set cmd2 = New ADODB.Command

cmd2.ActiveConnection = _

  "Provider=Microsoft.Jet.OLEDB.4.0;" & _

  "Data Source=MyDatabase.mdb;"

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.

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:

cmd.CommandText = "qupdChangeData".

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:

Dim rec As Recordset

cmd.CommandText = "Select * From Authors"

Set rec = cmd.Execute

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:

cmd.CommandText = "Delete * From Authors"

cmd.Execute intRecords

Msgbox intRecords & " records were deleted."

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.

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:

Dim WithEvents conn As ADODB.Connection

Dim cmd As ADODB.Command

Dim conn as ADODB.Connection



Set cmd = New ADODB.Command

cmd.ActiveConnection = _

  "Provider=Microsoft.Jet.OLEDB.4.0;" & _

  "Data Source=MyDatabase.mdb;"

cmd.CommandText = _

   "Update Authors Set Lname = 'Vogel'"

cmd.CommandType = adCmdText

Set conn = cmd.ActiveConnection

cmd.Execute



Private Sub conn_InfoMessage _

  (ByVal pError As ADODB.Error, _

   adStatus As ADODB.EventStatusEnum, _

   ByVal pConnection As ADODB.Connection)

       MsgBox Status

End Sub

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:

Set prm = cmd.Parameters.Item("MyParameter")

Set prm = cmd.Parameters("MyParameter")

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.

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.

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:

Set prm = cmd.CreateParameter

prm.Name = "MyParameter"

prm.Direction = adParamInput

prm.Size = 8

prm.Type = adVarChar

prm.Value = "MyValue"

cmd.Parameters.Append prm

cmd.Execute

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:

cmd.Parameters.Append cmd.CreateParameter _

  ("MyParameter", adVarChar, adParamInput, 8, _

   "MyValue")

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:

cmd.Execute , Array("MyValue1", "MyValue2")

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