Automating Microsoft Access with VBA

You can use the Connection or the Command object to retrieve and manipulate data. However, the Command object has a few advantages:

• Using a Command object, you can avoid a Recordset object and update data directly, which can be faster with some databases because the changes are made on the server instead of dragging the records across the network.

• The Command object supports parameters; the Connection object doesn't.

• The Command object has more properties than the Connection object, which enables you to fine-tune its behavior.

By way of a definition, you can compare the Command object to a stored procedure or other data access object that returns data because the Command object executes the process that actually returns the data. The object does so by executing code against the OLE DB data source or by retrieving data from the OLE DB data source. This makes the Command object a flexible tool to have around.

Creating a Command Object

Creating a Command object is similar to creating a Connection object: declare it and then define it. But you don't open it, which makes sense given the difference between the two objects. Use the following structure to declare and define a Command object:

Dim strConn As String Dim cmd As ADODB.Command strConn = connectionstring Set cmd = New ADODB.Command cmd.ActiveConnection = strConn

The Command's connectionstring argument is identical to that used by the Connection object.

Executing the Command Object

The Command object executes code against the data source that manipulates that data in some way either changing it at the source or retrieving it. Usually, you'll use a Recordset object to retrieve data, you'll read about that object a bit later.

To execute code that updates or retrieves data, use the Command object's CommandText property, which contains the actual instructions executed against the data (technically, the provider). This property is a string expression or value that contains a provider command, which can be a SQL statement, the name of a table, a URL, or a call to a stored procedure.

The following procedure automates a simple update query that increases each of the hourly rate values (in the Timetrack.mdb Tasks table) by three percent using a SQL UPDATE command directly against the data:

Private Sub EditCA() 'Update hourly rate values by 3 percent. Dim strConn As String Dim cmd As ADODB.Command Set cmd = New ADODB.Command With cmd .ActiveConnection = CurrentProject.Connection .CommandText = "UPDATE Tasks " & _ "SET HourlyRate = HourlyRate + (HourlyRate * .03)" .Execute End With Set cmd = Nothing End Sub

Enter the procedure into a standard procedure (or use Chapter 16's example module). With the cursor inside the procedure, press F5. Nothing seems to happen, but open the Tasks table and you can see that each value has been updated, as shown in Figure 16.3. The first value, $154.50, was $150.00. All the values have been increased.