ADO - Handling Batch Updates in ADO

Introduction

Handling Batch Updates is an advanced feature in ActiveX Data Objects (ADO) that allows multiple database changes to be collected in a Recordset and sent to the database in a single operation. Instead of updating the database immediately after every insert, update, or delete, ADO stores these changes temporarily in memory. Once all modifications are complete, they are committed together using the UpdateBatch method.

Batch updating is particularly useful in applications where users make several changes before saving, such as inventory management systems, employee management systems, customer relationship management (CRM) software, and financial applications. It reduces the number of database interactions, improves performance, and minimizes network traffic.

What are Batch Updates?

A batch update is the process of collecting multiple modifications to a Recordset and sending them to the database in one operation.

Normally, every time a record is modified, the application communicates with the database immediately.

For example:

Update Record 1
Update Record 2
Update Record 3
Update Record 4
Update Record 5

This creates five separate database operations.

With batch updating:

Modify Record 1
Modify Record 2
Modify Record 3
Modify Record 4
Modify Record 5

↓

UpdateBatch

Only one communication with the database occurs.

Why Batch Updates are Important

In large applications, thousands of records may be modified simultaneously.

If every modification is immediately written to the database:

  • More network traffic is generated.

  • Database connections remain active longer.

  • Server workload increases.

  • Application performance decreases.

Batch updating solves these problems by grouping all changes together.

Benefits include:

  • Faster execution

  • Reduced database load

  • Lower network usage

  • Better scalability

  • Efficient handling of multiple records

How Batch Updates Work

The batch update process generally follows these steps:

Step 1: Open Database Connection

The application connects to the database.

Application
      ↓
Database Connection Open

Step 2: Retrieve Data

The required records are loaded into a Recordset.

Database
     ↓
Recordset

Step 3: Enable Batch Updating

The Recordset must support batch locking.

rs.LockType = adLockBatchOptimistic

This lock type stores modifications until they are committed.

Step 4: Modify Records

Users can:

  • Edit records

  • Insert new records

  • Delete records

These changes remain in memory.

Step 5: Commit Changes

All modifications are saved together.

rs.UpdateBatch

ADO sends every pending change to the database.

Requirements for Batch Updates

To use batch updates successfully, certain Recordset properties must be configured.

Client-Side Cursor

rs.CursorLocation = adUseClient

The client-side cursor stores records in application memory, allowing multiple changes before updating the database.

Static Cursor

rs.CursorType = adOpenStatic

A static cursor provides a snapshot of the data, making it suitable for batch processing.

Batch Optimistic Lock

rs.LockType = adLockBatchOptimistic

This lock type allows changes to accumulate without immediately updating the database.

Creating a Recordset for Batch Updates

Example:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ConnectionString

rs.CursorLocation = adUseClient

rs.Open "SELECT * FROM Employees", con, adOpenStatic, adLockBatchOptimistic

The Recordset is now ready for batch updating.

Updating Existing Records

Example:

rs.MoveFirst

rs("Salary") = 75000

rs.Update

The salary change is stored in the Recordset but is not yet written to the database.

Adding New Records

Example:

rs.AddNew

rs("EmployeeID") = 105
rs("EmployeeName") = "Anita"
rs("Salary") = 50000

rs.Update

The new employee is added only to the Recordset.

Deleting Records

Example:

rs.MoveFirst

rs.Delete

The record is marked for deletion.

The actual deletion occurs after UpdateBatch.

Saving All Changes

Example:

rs.UpdateBatch

This single method performs:

  • All insert operations

  • All update operations

  • All delete operations

at one time.

Example Scenario

Suppose an HR department updates employee information.

The HR executive:

  • Changes salary of 50 employees

  • Adds 20 new employees

  • Removes 10 resigned employees

Without batch updating:

80 separate database updates

With batch updating:

One UpdateBatch operation

This significantly improves efficiency.

Internal Working of UpdateBatch

When records are modified:

Database

↓

Recordset

↓

Changes Stored in Memory

After calling:

rs.UpdateBatch

ADO compares each modified record with the database and applies the pending changes.

Detecting Pending Changes

ADO tracks which records have been modified.

Possible record states include:

  • Unchanged

  • Modified

  • New

  • Deleted

Only changed records are sent to the database during UpdateBatch.

Batch Conflict Handling

Sometimes another user changes the same record before batch updates are committed.

Example:

Employee Salary

Database

50000

User A changes it to:

55000

User B changes it to:

60000

If User A saves first, User B's update may conflict.

ADO detects such conflicts.

Developers can:

  • Accept database values.

  • Overwrite database values.

  • Ask the user to resolve the conflict.

  • Retry the update.

Canceling Pending Updates

If changes are no longer required:

rs.CancelBatch

All pending modifications are discarded.

The database remains unchanged.

Resynchronizing Data

If the database changes while the Recordset is disconnected:

rs.Resync

The Recordset retrieves the latest values from the database.

This helps reduce update conflicts.

Performance Benefits

Without Batch Updates

100 Records

↓

100 Database Requests

With Batch Updates

100 Records

↓

1 Database Request

This greatly reduces communication overhead.

Real-World Applications

Employee Management System

Human Resources updates employee salaries, departments, contact information, and job titles. Instead of saving each change individually, all modifications are committed together at the end of the editing session.

Inventory Management

Warehouse personnel update stock quantities, product prices, and supplier information throughout the day. All changes are saved in one batch to improve performance.

Banking Systems

Bank employees verify and update multiple customer accounts before committing all approved changes to the central database in a single operation.

Student Information System

College administrators update attendance records, examination results, and student profiles. Batch updating reduces the number of database transactions and speeds up the process.

Customer Relationship Management (CRM)

Sales representatives update customer details, meeting notes, and follow-up activities. Changes are accumulated and synchronized together, reducing server workload.

Advantages

  • Reduces the number of database operations.

  • Improves application performance.

  • Minimizes network traffic.

  • Supports offline editing.

  • Efficiently processes multiple record modifications.

  • Reduces database server load.

  • Enables easier conflict detection.

  • Suitable for enterprise applications with large datasets.

Limitations

  • Batch updates require additional client memory because changes are stored locally until committed.

  • Update conflicts may occur if multiple users modify the same records simultaneously.

  • Error handling becomes more important because one failed update can affect the overall batch.

  • Large batches may take longer to process and should be divided into manageable sizes when necessary.

  • Batch updates are not suitable for applications that require every change to be reflected immediately in the database.

Best Practices

  • Use adLockBatchOptimistic when planning to save multiple changes together.

  • Use a client-side cursor (adUseClient) to support batch operations.

  • Validate data before calling UpdateBatch.

  • Handle update conflicts gracefully and provide users with clear resolution options.

  • Keep batch sizes reasonable to avoid excessive memory usage.

  • Use CancelBatch if the user decides to discard pending changes.

  • Refresh data with Resync when working in environments where multiple users may update the same records.

  • Close Recordset and Connection objects after completing updates to release resources.

Conclusion

Handling Batch Updates in ADO is an efficient technique for managing multiple database modifications in a single operation. By storing inserts, updates, and deletions in memory and committing them together using the UpdateBatch method, applications reduce database communication, improve performance, and make better use of system resources. Batch updates are especially valuable in enterprise environments, offline applications, and systems that process large volumes of data, providing a scalable and reliable approach to database management.