ADO - Disconnected Recordsets and Offline Data Manipulation in ADO

Introduction

ActiveX Data Objects (ADO) is a Microsoft technology used to access and manipulate data stored in databases. One of its most powerful features is the Disconnected Recordset, which allows applications to retrieve data from a database, disconnect from it, and continue working with the data locally. This feature is especially useful in applications where maintaining a constant database connection is expensive or impractical.

In a traditional database application, the connection remains open while users browse or modify records. If many users keep their connections open, the database server may become overloaded. A disconnected Recordset solves this problem by storing the data in the application's memory, allowing the connection to be closed immediately after fetching the data.

Offline Data Manipulation refers to performing operations such as viewing, editing, adding, deleting, filtering, and sorting records while the Recordset is disconnected from the database. Once the work is complete, the Recordset can reconnect and synchronize all changes with the database.

What is a Disconnected Recordset?

A disconnected Recordset is a Recordset object that no longer has an active connection to the database but still contains all the retrieved data.

Normally, when a Recordset is created, it remains connected to the database through a Connection object. In a disconnected Recordset, the connection is removed after the data has been loaded.

The application can continue working with the data because the Recordset stores the information in memory.

Example:

Set rs.ActiveConnection = Nothing

This statement disconnects the Recordset from the database while preserving the retrieved records.

Need for Disconnected Recordsets

Consider an online shopping application where thousands of customers browse products simultaneously.

If every customer keeps an active database connection while reading product details, the database server must maintain thousands of open connections. This increases server load, memory usage, and network traffic.

Instead, the application can:

  1. Connect to the database.

  2. Retrieve product information.

  3. Disconnect from the database.

  4. Allow customers to browse the retrieved data.

  5. Reconnect only when an order is placed or updates are required.

This approach reduces unnecessary database connections and improves overall system performance.

How Disconnected Recordsets Work

The following steps explain the working process.

Step 1: Establish a Database Connection

The application creates a Connection object and connects to the database.

Dim con As New ADODB.Connection

con.Open ConnectionString

The connection is now active.

Step 2: Retrieve Records

A Recordset object retrieves the required records.

Dim rs As New ADODB.Recordset

rs.CursorLocation = adUseClient

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

The Recordset now contains the requested employee data.

Step 3: Disconnect the Recordset

The Recordset is disconnected from the database.

Set rs.ActiveConnection = Nothing

con.Close

Although the connection is closed, the Recordset still contains all employee records.

Step 4: Work with Data Offline

The application can now perform various operations without communicating with the database.

Examples include:

  • Viewing records

  • Searching records

  • Sorting records

  • Filtering records

  • Editing records

  • Adding records

  • Deleting records

Since these operations occur in memory, they are generally faster than repeatedly accessing the database.

Step 5: Reconnect

When the user finishes working with the data, the Recordset reconnects to the database.

con.Open ConnectionString

Set rs.ActiveConnection = con

Step 6: Synchronize Changes

All pending modifications are sent to the database.

rs.UpdateBatch

The database is now updated with all offline changes.

Components Required

A disconnected Recordset mainly uses the following ADO objects.

Connection Object

The Connection object establishes communication with the database.

Example:

Dim con As New ADODB.Connection

Its purpose is to open and close the database connection.

Recordset Object

The Recordset stores the retrieved records.

Example:

Dim rs As New ADODB.Recordset

After disconnection, it continues to hold all retrieved data.

Client-Side Cursor

A disconnected Recordset requires a client-side cursor.

Example:

rs.CursorLocation = adUseClient

The client-side cursor stores records in the application's memory instead of relying on the database server.

Static Cursor

A static cursor creates a snapshot of the data.

adOpenStatic

The Recordset does not automatically reflect changes made by other users after the data has been retrieved.

Batch Optimistic Locking

Example:

adLockBatchOptimistic

This locking mode allows multiple updates to be stored locally and committed together later using the UpdateBatch method.

Offline Data Manipulation

Reading Records

Users can navigate through records normally.

rs.MoveFirst

Do Until rs.EOF

    MsgBox rs("EmployeeName")

    rs.MoveNext

Loop

No database connection is required.

Updating Records

Existing records can be modified.

rs.MoveFirst

rs("Salary") = 60000

rs.Update

The modification is stored in memory.

Adding New Records

New records can be added.

rs.AddNew

rs("EmployeeID") = 110

rs("EmployeeName") = "Ravi"

rs("Salary") = 45000

rs.Update

The new record remains in the Recordset until synchronization.

Deleting Records

Records can also be removed.

rs.Delete

The deletion is marked locally.

Searching Records

The Find method searches records quickly.

rs.Find "EmployeeName='John'"

This operation works even though the Recordset is disconnected.

Filtering Records

A filter displays only selected records.

rs.Filter = "Department='Sales'"

Only Sales department employees appear.

Sorting Records

Sorting arranges records.

rs.Sort = "Salary DESC"

Employees are displayed from highest salary to lowest.

Batch Updating

Instead of updating each record individually, ADO allows batch updates.

Suppose an HR department modifies 500 employee records.

Without batch updates:

Update Record 1

Update Record 2

Update Record 3

...

Update Record 500

This requires 500 database operations.

With batch updates:

UpdateBatch

Only one synchronization process is required.

This reduces network traffic and improves performance.

Advantages of Disconnected Recordsets

Reduced Database Connections

Connections remain open only while retrieving or saving data.

Better Performance

Most operations occur in memory, making them faster.

Lower Network Traffic

Only two database communications are required:

  • Retrieving data

  • Updating data

Improved Scalability

A database server can support more users because connections are not occupied unnecessarily.

Offline Capability

Applications continue functioning even if the network connection is temporarily unavailable.

Efficient Resource Usage

Database memory, CPU, and network bandwidth are conserved.

Limitations

Memory Consumption

Large Recordsets occupy significant client memory.

Synchronization Conflicts

If another user modifies the same record before synchronization, conflicts may occur.

Example:

Employee Salary = ₹40,000

User A changes it to ₹45,000 offline.

User B changes it to ₹42,000 online.

When User A reconnects, the application must determine which value should be saved.

Not Suitable for Real-Time Data

Applications requiring continuously updated information, such as stock trading or live monitoring systems, should maintain active connections instead of using disconnected Recordsets.

Real-World Applications

Sales Management

Sales representatives download customer information before traveling. They update customer records during visits and synchronize the changes after reconnecting to the office network.

Hospital Management

Doctors retrieve patient records before rounds. They update diagnoses and treatment details while moving between departments, then synchronize the changes with the hospital database.

Inventory Management

Warehouse staff scan products in areas with poor connectivity. Stock updates are stored locally and uploaded later.

Banking Applications

Field officers collect customer information in remote villages where internet connectivity is limited. Data is synchronized once they return to an area with network access.

Retail Stores

Billing systems continue recording sales during temporary internet outages. Once connectivity is restored, all transactions are synchronized with the central database.

Mobile Applications

Mobile business applications often download data at the start of the day, allow users to work offline, and synchronize all updates when an internet connection becomes available.

Best Practices

  • Use adUseClient as the cursor location.

  • Use adOpenStatic for disconnected Recordsets.

  • Use adLockBatchOptimistic for efficient batch updates.

  • Retrieve only the required records instead of entire tables.

  • Validate data before updating the database.

  • Handle synchronization conflicts carefully.

  • Close database connections immediately after retrieving data.

  • Release Recordset and Connection objects after use to free memory.

  • Keep offline sessions as short as possible to reduce the chances of update conflicts.

Summary

Disconnected Recordsets are a powerful feature of ADO that enable applications to retrieve data, disconnect from the database, and continue working with the data in memory. This approach minimizes database connection time, reduces server workload, improves application performance, and supports offline data manipulation. By combining client-side cursors, batch optimistic locking, and batch updates, developers can build scalable, efficient, and user-friendly database applications that perform well even in environments with limited or intermittent network connectivity.