|< Day Day Up >|
You can use the Connection or the Command object to retrieve and manipulate data. However, the Command object has a few advantages:
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.
Figure 16.3. A Command object updated the hourly rate values.
|< Day Day Up >|