ADO - ADO Recordset UpdateBatch Method

The UpdateBatch method in ADO is used to apply multiple pending changes made to a Recordset to the underlying data source at one time. It is mainly associated with batch updating, where changes such as adding, modifying, or deleting records are temporarily stored in the client-side Recordset and then sent to the database together.

What is UpdateBatch?

Normally, when a record is modified in a database application, the change may be sent to the database immediately. However, ADO can work in a mode where changes are kept temporarily in memory. This allows an application to make several changes without immediately sending each individual change to the database.

The UpdateBatch method submits these pending changes as a batch.

The general syntax is:

recordset.UpdateBatch AffectRecords

Here, recordset represents the ADO Recordset object, while AffectRecords is an optional parameter that specifies which records should be affected.

Why is UpdateBatch Used?

UpdateBatch is useful when an application needs to make several changes efficiently. Instead of sending every modification separately, the application can collect the changes and submit them together.

For example, suppose an employee-management application needs to update the salaries of 20 employees. With batch updating, the application can modify the records in the Recordset first and then call UpdateBatch to send the pending changes to the database.

This can reduce the number of interactions between the application and the database.

How UpdateBatch Works

The typical process involves the following steps:

1. Open a Recordset with an appropriate cursor and locking configuration.

2. Make changes to one or more records.

3. ADO keeps these changes as pending updates.

4. Continue making additional changes if required.

5. Call the UpdateBatch method.

6. ADO attempts to apply the pending changes to the data source.

7. If conflicts occur, the application can examine the affected records and handle the errors.

A simplified example is:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, Salary FROM Employees", _
        conn, adOpenKeyset, adLockBatchOptimistic

rs.Fields("Salary").Value = 50000
rs.Update

rs.MoveNext

rs.Fields("Salary").Value = 55000
rs.Update

rs.UpdateBatch

In this example, the Recordset uses adLockBatchOptimistic, which allows changes to be accumulated before they are submitted to the database.

Relationship with Batch Optimistic Locking

The UpdateBatch method is closely associated with batch optimistic locking.

The locking mode is generally specified when opening the Recordset:

rs.Open sql, conn, adOpenKeyset, adLockBatchOptimistic

With adLockBatchOptimistic, changes are not necessarily written to the database immediately. Instead, they remain pending until the application calls UpdateBatch.

This approach is particularly useful for applications that need to work with several records before committing the changes.

Updating Multiple Records

One of the main advantages of UpdateBatch is that several changes can be accumulated.

For example:

rs.MoveFirst

Do Until rs.EOF
    rs.Fields("Status").Value = "Active"
    rs.Update
    rs.MoveNext
Loop

rs.UpdateBatch

Here, the application modifies the Status field for multiple records. The changes are accumulated and then submitted through UpdateBatch.

The exact behavior depends on the cursor, provider, locking mode, and data source being used.

Handling Conflicts

Batch updates can produce conflicts when another user changes the same data before the pending changes are applied.

For example, consider a customer record whose balance is initially ₹10,000. An application retrieves the record and changes the balance to ₹12,000. Before UpdateBatch is called, another application changes the same record.

When the first application finally calls UpdateBatch, the provider may detect a conflict.

The application can then inspect the Recordset and error information to determine what happened and decide how to handle the conflict.

Canceling Pending Changes

UpdateBatch should be distinguished from CancelBatch.

UpdateBatch attempts to send pending changes to the data source:

rs.UpdateBatch

CancelBatch is used when the application wants to discard pending batch changes:

rs.CancelBatch

For example:

rs.Fields("Salary").Value = 60000
rs.Update

rs.CancelBatch

In this situation, the pending batch changes are canceled instead of being submitted.

Therefore:

• UpdateBatch applies pending changes.

• CancelBatch discards pending changes.

Advantages of UpdateBatch

The UpdateBatch method provides several benefits:

Reduced database communication: Multiple changes can be submitted together rather than communicating with the database after every individual modification.

Better control over updates: The application can review or process several changes before submitting them.

Useful for disconnected applications: Batch updating can be useful when working with disconnected Recordsets where data is modified away from the original database connection.

Conflict detection: Batch updates can allow an application to identify conflicts that occur when multiple users modify the same data.

Improved organization: Applications can separate the process of editing records from the process of submitting those changes.

Important Considerations

UpdateBatch does not mean that every provider will handle batch operations in exactly the same way. Its behavior depends on the ADO provider, data source, cursor type, locking mode, and other Recordset settings.

The Recordset must also be opened with a locking configuration that supports batch updates. Simply calling UpdateBatch on any Recordset does not guarantee that the operation will work.

Applications should also handle errors carefully because individual records may encounter conflicts or other database-related problems during the batch operation.

UpdateBatch vs Update

The Update and UpdateBatch methods serve different purposes.

For example:

rs.Fields("Name").Value = "Rahul"
rs.Update

The Update method completes the modification of the current Recordset record.

With batch processing:

rs.Fields("Name").Value = "Rahul"
rs.Update

rs.UpdateBatch

The individual record modification is prepared as a pending batch change, and UpdateBatch is used to submit the accumulated changes.

Conclusion

The ADO Recordset UpdateBatch method provides a mechanism for submitting multiple pending Recordset changes to a database in a batch. It is especially useful with batch optimistic locking, disconnected Recordsets, and applications that need to accumulate several modifications before sending them to the data source.

Understanding UpdateBatch is important because it demonstrates how ADO can separate editing data from submitting changes, giving applications greater control over database updates and conflict handling.