Modifying Table Data Using ADO Code
So far, this chapter has only covered the process of retrieving data from a recordset. It is common that you might need to update the data in a recordset. The sections that follow show you how to change data one record at a time, update a batch of records, delete records, and add records.
Changing Record Data One Record at a Time
It is possible to loop through a recordset, modifying all the records in the recordset. Listing 14.28 shows this technique.
Listing 14.28 Modifying One Record At a Time
Sub IncreaseEstimate()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strSQL As String
Dim lngUpdated As Long
'Establish the connection, cursor type,
'and lock type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open ("Select * from tblProjectsChange")
strSQL = "ProjectTotalEstimate < 30000"
lngUpdated = 0
'Find the first row meeting the designated criteria
rst.Find strSQL
'Loop through the recordset, locating all rows meeting
'the designated criteria, increasing the ProjecTotalEstimate
'field by 10%
Do Until rst.EOF
lngUpdated = lngUpdated + 1
rst("ProjectTotalEstimate") = rst("ProjectTotalEstimate") * 1.1
rst.Update
rst.Find strSQL, 1, adSearchForward
Loop
'Print how many rows are updated
Debug.Print lngUpdated & " Records Updated"
rst.Close
Set rst = Nothing
End Sub
The code in Listing 14.28 opens a recordset based on all the records in the tblProjectsChange table. It locates the first record where the ProjectTotalEstimate is less than 30,000. The ProjectTotalEstimate is increased by 10%, and the record is updated. The code locates the next record that meets the specified criteria. The code repeats the process until it locates all records meeting the specified criteria.
This code is very inefficient from several standpoints. The first problem is that it opens a recordset based on all the records in the tblProjectsChange table, when only those with a ProjectTotalEstimate less than 30,000 needed to be updated. A more efficient approach is to open a recordset containing only those records that you need to update. Listing 14.29 illustrates this technique.
Listing 14.29 Improving the Process of Modifying One Record At a Time
Sub IncreaseEstimateImproved()
'Declare and instantiate a recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim lngUpdated As Long
'Establish the connection, cursor type,
'and lock type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open ("Select * from tblProjectsChange " & _
"WHERE ProjectTotalEstimate < 30000")
'Loop through the recordset, locating all rows meeting
'the designated criteria, increasing the ProjecTotalEstimate
'field by 10%
Do Until rst.EOF
lngUpdated = lngUpdated + 1
rst("ProjectTotalEstimate") = rst("ProjectTotalEstimate") * 1.1
rst.Update
rst.MoveNext
Loop
'Print how many rows are updated
Debug.Print lngUpdated & " Records Updated"
rst.Close
Set rst = Nothing
End Sub
Furthermore, it would be more efficient to simply execute an action query that performs the update. This technique is covered in the section that follows.
CAUTION
If you're accustomed to DAO, you might be quite surprised by the behavior of ADO. Whereas DAO requires that the Edit method be used before field values are assigned, no Edit method is used with ADO. Furthermore, if you forget to issue the Update method on a DAO recordset, the record is not updated. On the other hand, with ADO, the Update method is implied. The update occurs automatically as soon as the record pointer is moved. These behavior differences can lead to big surprises!
Performing Batch Updates
If you use a client-side cursor, along with a static or keyset cursor, you can take advantage of batch updates. Using batch updates, all changes you make to a recordset are sent to the underlying OLEDB provider as a batch. The process is illustrated in Listing 14.30.
Listing 14.30 Performing Batch Updates
Sub BatchUpdates()
'Declare and instantiate a recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strSQL As String
Dim lngUpdated As Long
'Establish the connection, cursor type, cursor
'location, and lock type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseClient
rst.LockType = adLockBatchOptimistic
rst.Open ("Select * from tblProjectsChange")
strSQL = "ProjectTotalEstimate < 30000"
lngUpdated = 0
'Find the first row meeting the designated criteria
rst.Find strSQL
'Loop through the recordset, locating all rows meeting
'the designated criteria, increasing the ProjecTotalEstimate
'field by 10%
Do Until rst.EOF
lngUpdated = lngUpdated + 1
rst("ProjectTotalEstimate") = rst("ProjectTotalEstimate") * 1.1
rst.Find strSQL, 1, adSearchForward
Loop
'Send all changes to the provider
rst.UpdateBatch
'Print how many rows are updated
Debug.Print lngUpdated & " Records Updated"
rst.Close
Set rst = Nothing
End Sub
In the example, the CursorLocation property of the recordset is set to adUseClient, the CursorType is set to adOpenKeyset, and the LockType is set to adLockBatchOptimistic. Notice that the Update method is not included in the Do Until loop. Instead, the UpdateBatch method is used to send all of the changes to the server at once.
Making Bulk Changes
As mentioned in the previous section, it is inefficient to open a recordset and then update each record individually. It is much more efficient to execute an action query. Listing 14.31 illustrates this process.
Listing 14.31 Making Bulk Changes to the Records in a Recordset
Sub RunUpdateQuery()
'Declare and instantiate a Connection object
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
'Establish the connection and execute an action query
Set cnn = CurrentProject.Connection
cnn.Execute "qryIncreaseTotalEstimate"
cnn.Close
End Sub
In Listing 14.31, the Execute method of the Connection object executes a stored query called qryIncreaseTotalEstimate. Any criteria contained within the query are applied.
Deleting an Existing Record
You can use ADO code to delete a record in a recordset. The code appears in Listing 14.32. Note that it must be called using basADORecordset.DeleteCusts.
Listing 14.32 Deleting an Existing Record
Sub DeleteCusts(lngProjEst As Long)
'Declare and instantiate a recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish the connection, cursor type,
'and lock type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "Select * from tblProjectsChange"
intCounter = 0
'Loop through the recordset, deleting all projects
'with an estimate lower than the specified amount
Do Until rst.EOF
If rst("ProjectTotalEstimate") < lngProjEst Then
rst.Delete
intCounter = intCounter + 1
End If
If Not rst.EOF Then
rst.MoveNext
End If
Loop
'Designate how many customers were deleted
Debug.Print intCounter & " Customers Deleted"
rst.Close
Set rst = Nothing
End Sub
In Listing 14.32, a recordset is opened, based on all the records in the tblProjectsChange table. The code loops through all the records in the recordset. If the ProjectTotalEstimate is less than the value passed as a parameter to the routine, the Delete method of the Recordset object removes the record from the recordset.
As previously discussed, this example is very inefficient. You should either build a recordset containing only the records you want to delete, or use an action query to accomplish the task.
TIP
If you are using a provider that supports stored procedures, it is most efficient to add, edit, and delete data using a stored procedure. Stored procedures execute on the server, sending no data over the network wire.
Adding a New Record
Not only can you edit and delete data using ADO, but you can also add records as well. Listing 14.33 illustrates this process.
Listing 14.33 Adding a New Record to a Recordset
Private Sub cmdAddADO_Click()
Dim rst As ADODB.Recordset
'Ensure that the project name and ClientID are entered
If IsNull(Me.txtProjectName) Or _
IsNull(Me.cboClientID) Then
MsgBox "The Project Name and Client Must be Filled In"
Else
'Instantiate a recordset
Set rst = New ADODB.Recordset
'Set the connection, cursor type, and lock type,
'and open the recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from tblProjectsChange Where ProjectID = 0"
'Add a new row to the recordset, populating its values with
'the controls on the form
.AddNew
!ProjectName = Me.txtProjectName
!ProjectDescription = Me.txtProjectDescription
!ClientID = Me.cboClientID
.Update
'Populate the txtProjectID text box with the
'autonumber value assigned to the new row
Me.txtProjectID = !ProjectID
End With
End If
End Sub
This code, an event procedure for a command button on frmUnbound, begins by setting the CursorType property of the recordset to adOpenKeyset and the LockType property to adLockOptimistic. The AddNew method creates a buffer for a new record. All the field values are assigned, based on values in the text boxes on the form. The Update method writes the data to disk. Because the ProjectID field is an Autonumber field, the txtProjectID text box must be updated to reflect the Autonumber value that was assigned.
CAUTION
With DAO, you are not placed on the new record after it is added. With ADO, you are moved to the new record when you issue the Update method.