ADO - ADO Recordset CancelBatch Method

The CancelBatch method in ADO is used to cancel a group of pending changes made to a Recordset when the Recordset is configured to use batch updating. Instead of immediately sending every modification to the database, ADO can temporarily keep changes such as additions, modifications, and deletions in memory. The CancelBatch method allows these pending changes to be discarded before they are permanently applied to the database.

What Is Batch Updating?

Normally, when a record is modified and the Update method is called, the change may be sent directly to the data source. With batch updating, several changes can be collected first and then submitted together.

For example, suppose a Recordset contains employee information:

Employee ID Name Department
101 Arun Sales
102 Ravi Finance
103 Meena HR

A user might modify Ravi's department, change Meena's department, and delete Arun's record. With batch updating, these changes can remain pending instead of being immediately sent to the database.

If the user later decides that none of these changes should be applied, CancelBatch can be used to discard the pending modifications.

Syntax

The basic syntax is:

recordset.CancelBatch

The method can also accept an optional AffectRecords argument:

recordset.CancelBatch AffectRecords

AffectRecords specifies which pending records should have their changes cancelled.

Common ADO constants include:

adAffectCurrent
adAffectGroup
adAffectAll

These constants determine whether the operation applies to the current record, a group of records, or all records with pending changes.

How CancelBatch Works

The general process is:

  1. A connection is established with the database.

  2. A Recordset is opened using an appropriate cursor and lock type.

  3. Batch updating is enabled.

  4. One or more records are added, modified, or deleted.

  5. The changes remain pending.

  6. CancelBatch is called.

  7. The pending changes are discarded.

  8. The original database values remain unchanged.

For example:

rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic

rs.Open "SELECT * FROM Employees", conn

rs.Fields("Department") = "Marketing"
rs.Update

rs.CancelBatch

Here, the change to the Department field is treated as a pending batch update. Calling CancelBatch cancels the pending change rather than applying it to the database.

CancelBatch vs UpdateBatch

CancelBatch and UpdateBatch perform opposite operations.

UpdateBatch sends pending changes to the data source:

rs.UpdateBatch

CancelBatch discards pending changes:

rs.CancelBatch

For example:

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

rs.UpdateBatch

The pending salary change is submitted to the database.

In contrast:

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

rs.CancelBatch

The pending salary change is cancelled.

Therefore, a simple way to remember the difference is:

UpdateBatch = apply pending changes

CancelBatch = discard pending changes

Why Is CancelBatch Useful?

The method is particularly useful when an application allows users to review several modifications before saving them.

Consider an employee-management application where an administrator edits several employee records. The application can allow the administrator to make changes and review them before committing them to the database.

If the administrator selects Cancel, the application can use CancelBatch to discard the pending modifications.

This reduces the need to immediately write every modification to the database.

Canceling Changes for Specific Records

The optional AffectRecords parameter can be used when the application needs more control over which pending changes are cancelled.

For example:

rs.CancelBatch adAffectCurrent

This requests cancellation of the pending changes associated with the current record.

To cancel all pending changes:

rs.CancelBatch adAffectAll

The exact behavior depends on the Recordset configuration and provider support, so applications should use an appropriate cursor, locking mode, and data provider.

Relationship with Batch Optimistic Locking

CancelBatch is commonly associated with batch optimistic locking.

With optimistic locking, records are generally not locked for a long period while users are editing them. Instead, changes are collected and later submitted to the database.

A typical workflow is:

Open Recordset
       |
       v
Edit several records
       |
       v
Changes remain pending
       |
       +------------------+
       |                  |
       v                  v
UpdateBatch          CancelBatch
       |                  |
       v                  v
Save changes         Discard changes

This gives an application the ability to let users decide whether their changes should be committed.

Important Considerations

CancelBatch does not mean that an already committed database transaction is rolled back. It is specifically concerned with pending batch changes in the Recordset.

This distinction is important.

For example, if changes have already been successfully submitted using:

rs.UpdateBatch

calling CancelBatch afterward does not generally undo those already committed database changes.

Database transactions use separate mechanisms such as:

conn.BeginTrans
conn.CommitTrans
conn.RollbackTrans

Therefore, CancelBatch should not be confused with RollbackTrans.

Difference Between CancelUpdate and CancelBatch

Another important distinction is between CancelUpdate and CancelBatch.

CancelUpdate is generally used to cancel changes to the current record that have not yet been successfully updated.

Example:

rs.Fields("Name") = "New Name"
rs.CancelUpdate

CancelBatch, on the other hand, is designed for cancelling pending batch changes.

Method Main Purpose
CancelUpdate Cancels changes to the current record
CancelBatch Cancels pending batch updates
Update Updates the current record
UpdateBatch Sends pending batch changes to the data source
RollbackTrans Rolls back a database transaction

Understanding these differences prevents incorrect use of ADO's update mechanisms.

Practical Example

Suppose an application allows an administrator to update employee salaries.

rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic

rs.Open "SELECT EmployeeID, Name, Salary FROM Employees", conn

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

'Administrator reviews the changes

rs.CancelBatch

In this example, the salary modification is held as a pending batch change. When CancelBatch is called, the pending modification is discarded.

If the administrator instead chooses to save the changes:

rs.UpdateBatch

the pending changes are submitted to the database.

Advantages

The CancelBatch method provides several benefits:

  1. It allows users to discard multiple pending modifications.

  2. It supports applications that require a review-before-save workflow.

  3. It works naturally with batch optimistic locking.

  4. It reduces the need to individually reverse multiple Recordset changes.

  5. It provides greater control over when modifications are submitted to the data source.

Limitations

CancelBatch is not a replacement for database transactions. Its behavior depends on the Recordset configuration and the capabilities of the underlying provider.

It should also not be used with the assumption that it can undo changes that have already been successfully committed to the database.

Conclusion

The ADO Recordset CancelBatch method is used to discard pending changes in a Recordset operating with batch updating. It is especially useful when an application allows multiple records to be edited before the user decides whether to save or cancel the changes.

The key distinction to remember is:

CancelBatch  → Discards pending batch changes
UpdateBatch  → Applies pending batch changes
CancelUpdate → Cancels changes to the current record
RollbackTrans → Rolls back a database transaction

Thus, CancelBatch provides an important mechanism for managing and safely discarding groups of pending Recordset modifications before they are submitted to the database.