HandsOn Performing Batch Updates

1. Switch to the Visual Basic Editor window and insert a new module.

2. In the Code window, enter the BatchUpdate_Records procedure as shown below.

Sub BatchUpdate_Records()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strConn As String Dim strCriteria As String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

strCriteria = "[Title] = 'Sales Representative'"

Set conn = New ADODB.Connection conn.Open strConn

Set rst = New ADODB.Recordset

With rst

Set .ActiveConnection = conn .Source = "Employees" .CursorLocation = adUseClient .LockType = adLockBatchOptimistic .CursorType = adOpenKeyset .Open

.Find strCriteria Do While Not .EOF

.Fields("Title") = "Sales Rep" .Find strCriteria, 1 Loop

.UpdateBatch End With rst.Close Set rst = Nothing Set conn = Nothing End Sub

The BatchUpdate_Records procedure uses the ADO Find method to locate all the records that need to be modified. Once the first record is located, it is changed in memory and the Find operation goes on to search for the next record and so on until the end of the recordset is reached. Notice that the following statement is issued to search past the current record:

.Find strCriteria, 1

Creating and Manipulating Databases with ADO

Once all the records have been located and changed, the changes are all committed to the database in a single operation by issuing the UpdateBatch statement.

0 0

Post a comment