ADO Recordset Paging and Batch Updating are techniques used to efficiently handle large amounts of database data. When an application retrieves thousands or millions of records at once, it can consume significant memory and network resources. Paging allows an application to retrieve and process data in smaller groups, while batch updating allows multiple changes to be collected and sent to the database together. These techniques are particularly useful in applications that work with large datasets.

1. What Is Recordset Paging?

Recordset paging means dividing a large set of database records into smaller sections called pages. Instead of retrieving every record at once, an application retrieves only a specific number of records at a time.

For example, suppose a database contains 10,000 employee records. Displaying all 10,000 records on one page would be inefficient. Instead, an application could display:

• Page 1: Records 1–100

• Page 2: Records 101–200

• Page 3: Records 201–300

• ...

• Page 100: Records 9,901–10,000

The user can navigate between pages without loading the entire dataset into the application's interface at once.

2. PageSize Property

ADO Recordset provides the PageSize property to specify how many records should be included in one page.

For example:

rs.PageSize = 100

This specifies that each page should contain up to 100 records.

The PageSize property works together with the PageCount property. After setting the page size, PageCount indicates the number of pages available.

rs.PageSize = 100

Response.Write "Total Pages: " & rs.PageCount

If the Recordset contains 450 records and the page size is 100, the number of pages will be 5.

3. AbsolutePage Property

The AbsolutePage property is used to move to a particular page within a Recordset.

For example:

rs.PageSize = 100
rs.AbsolutePage = 2

This moves the current position to the beginning of the second page.

If there are 100 records per page, page 2 begins with record 101.

A typical paging operation can therefore involve:

rs.PageSize = 100
rs.AbsolutePage = 2

The application can then process the records belonging to that page.

4. AbsolutePosition Property

The AbsolutePosition property identifies the position of the current record within the Recordset.

For example:

Response.Write rs.AbsolutePosition

If the current record is the 250th record, the value can indicate its position as 250, depending on the cursor and provider capabilities.

This property can be useful when an application needs to determine the current record's location while implementing navigation.

5. Example of Recordset Paging

Consider a table called Employees:

SELECT EmployeeID, EmployeeName, Department
FROM Employees
ORDER BY EmployeeID

An ADO application could retrieve the Recordset and configure paging:

Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT EmployeeID, EmployeeName, Department FROM Employees ORDER BY EmployeeID", _
        conn, 3, 1

rs.PageSize = 20

The application can determine the number of pages:

Response.Write "Total Pages: " & rs.PageCount

To display page 3:

rs.AbsolutePage = 3

The application can then iterate through the records on that page:

For i = 1 To rs.PageSize
    If rs.EOF Then Exit For

    Response.Write rs("EmployeeName") & "<br>"
    rs.MoveNext
Next

This approach prevents the application interface from attempting to display thousands of records simultaneously.

6. Advantages of Recordset Paging

Recordset paging provides several benefits.

Reduced Memory Usage

Processing a smaller portion of a large dataset can reduce the amount of data that an application needs to handle at a particular time.

Improved User Experience

Users can view manageable groups of records instead of scrolling through a very large dataset.

Better Organization

Paging makes it easier to create interfaces containing controls such as:

First | Previous | 1 | 2 | 3 | 4 | Next | Last

Easier Data Navigation

Users can quickly move between sections of a large Recordset.

However, the exact performance benefits depend on the database provider, cursor type, query, and whether the provider actually retrieves the entire Recordset behind the scenes.

7. What Is Batch Updating?

Batch updating is a technique in which several changes made to a Recordset are accumulated locally and then submitted to the database together.

Without batch updating, an application may send an update to the database after every individual change.

For example:

Update Employee 1
Update Employee 2
Update Employee 3
Update Employee 4

With batch updating, the application can make several changes and then submit them together:

Modify Employee 1
Modify Employee 2
Modify Employee 3
Modify Employee 4
        |
        v
Submit changes as a batch

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

8. BatchOptimistic Locking

ADO supports batch updates through the LockType property.

A commonly used setting is:

rs.LockType = adLockBatchOptimistic

With batch optimistic locking, changes can be made to the Recordset and later submitted using the UpdateBatch method.

For example:

rs.LockType = adLockBatchOptimistic

The application can modify records:

rs("Salary") = 50000
rs.Update

The change is maintained for batch processing rather than necessarily being immediately written to the database.

After making multiple changes, the application can call:

rs.UpdateBatch

This sends pending changes to the data source.

9. Basic Batch Update Example

Suppose an application retrieves employee information:

Set rs = Server.CreateObject("ADODB.Recordset")

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

The application can modify a record:

rs("Salary") = 60000
rs.Update

It can then move to another record:

rs.MoveNext

Another record can be modified:

rs("Salary") = 65000
rs.Update

After completing several modifications:

rs.UpdateBatch

The pending changes are submitted to the database.

10. UpdateBatch Method

The UpdateBatch method is used to write pending batch changes to the underlying data source.

The basic syntax is:

rs.UpdateBatch

It can also be used with a specified affecting-records option in environments that support it:

rs.UpdateBatch adAffectAll

The exact behavior can depend on the provider and cursor configuration.

11. CancelBatch Method

Sometimes an application may decide not to submit pending changes.

ADO provides the CancelBatch method for this purpose.

rs.CancelBatch

For example, an application might allow a user to edit several employee records and then provide two options:

Save Changes
Cancel Changes

If the user chooses Cancel, the application can use:

rs.CancelBatch

This discards pending batch changes that have not yet been successfully submitted.

12. Batch Updating and Error Handling

Batch operations can encounter conflicts or errors. For example, another user may have changed a record after the application originally retrieved it.

This is one of the important considerations of optimistic concurrency.

An application should therefore check for errors after calling:

rs.UpdateBatch

The ADO Errors collection can provide information about database-related errors.

Conceptually:

On Error Resume Next

rs.UpdateBatch

If Err.Number <> 0 Then
    Response.Write "Batch update failed."
End If

Production applications should implement more robust error handling rather than relying only on Err.Number.

13. Paging and Batch Updating Together

Paging and batch updating can be useful in applications that display and edit large datasets.

Consider an employee management application containing 5,000 employees.

Instead of displaying all employees:

5,000 records

the application could display:

Page 1: 50 records
Page 2: 50 records
Page 3: 50 records
...

Suppose the administrator edits several employees on page 1.

The application can maintain those modifications as batch changes and eventually call:

rs.UpdateBatch

This provides a combination of manageable data presentation and grouped database updates.

14. Important Considerations

Recordset paging and batch updating are not automatically the best solution for every application.

The effectiveness of paging depends on the cursor type and database provider. Some providers may retrieve more data than the application actually displays.

Similarly, batch updating requires appropriate cursor and locking support. The provider must support the necessary ADO features for the operation to work as intended.

For modern applications, server-side SQL pagination is often preferable to loading a very large Recordset and relying entirely on client-side paging. For example, databases commonly provide mechanisms such as OFFSET/FETCH, LIMIT/OFFSET, or window functions.

15. Paging vs Batch Updating

Feature	Recordset Paging	Batch Updating
Main purpose	Divide records into pages	Submit multiple changes together
Primary property/method	PageSize, AbsolutePage	LockType, UpdateBatch
Main benefit	Easier handling of large result sets	Reduces individual update operations
Navigation	Uses pages	Not primarily a navigation feature
Modification	Not its main purpose	Designed for grouped modifications
Important consideration	Cursor/provider support	Provider and concurrency support

16. Summary

ADO Recordset Paging divides a large Recordset into manageable pages using properties such as PageSize, PageCount, and AbsolutePage. It is useful when an application needs to present or process large numbers of records in smaller groups.

ADO Batch Updating allows multiple Recordset changes to be collected and submitted together. It commonly uses adLockBatchOptimistic, UpdateBatch, and CancelBatch. This approach can reduce the number of database update operations and is particularly useful when several related changes need to be submitted as a group.

Together, these techniques provide useful mechanisms for managing large Recordsets and grouped modifications in classic ADO applications, although modern applications often combine database-level pagination with more direct update strategies for better scalability.