ADO - Advanced Cursor Service Techniques in ADO

Introduction

In ActiveX Data Objects (ADO), a cursor is a mechanism that allows an application to navigate through records retrieved from a database. While basic cursor operations such as moving to the next or previous record are commonly used, ADO also provides advanced cursor service techniques that enhance data handling, improve performance, and enable sophisticated data manipulation.

Cursor services determine how records are stored, accessed, updated, and synchronized between the application and the database. Understanding these advanced techniques helps developers build efficient, scalable, and responsive database applications.

What is a Cursor?

A cursor is an object that points to a specific record within a Recordset. It controls how an application moves through records and whether changes made by other users are visible.

For example, when a query retrieves employee information, the cursor determines:

  • How records are fetched.

  • Whether records can be edited.

  • Whether newly added records appear automatically.

  • Whether deleted records are visible.

  • How efficiently data is stored and accessed.

Without a cursor, an application would not be able to navigate or manipulate records effectively.

What is Cursor Service?

Cursor Service is a component provided by ADO that manages Recordsets, particularly when using client-side cursors. Instead of relying entirely on the database server, cursor services allow many data operations to be handled on the client computer.

Cursor services support features such as:

  • Offline data access

  • Sorting records

  • Filtering records

  • Searching records

  • Batch updates

  • Bookmark navigation

  • Record caching

This reduces the workload on the database server and improves overall application performance.

Client-Side Cursor vs Server-Side Cursor

Client-Side Cursor

A client-side cursor stores the retrieved records in the application's memory.

rs.CursorLocation = adUseClient

Characteristics:

  • Data is copied from the database to the client.

  • Supports disconnected Recordsets.

  • Allows sorting and filtering without querying the database again.

  • Reduces database connection time.

  • Ideal for distributed applications.

Example workflow:

Database
     ↓
Records Retrieved
     ↓
Client Memory
     ↓
Application Works Offline

Server-Side Cursor

A server-side cursor keeps the records on the database server.

rs.CursorLocation = adUseServer

Characteristics:

  • Requires an active database connection.

  • Uses less client memory.

  • Suitable for large datasets.

  • Reflects changes made by other users more easily.

Example workflow:

Application
      ↓
Database Server
      ↓
Records Accessed Directly

Advanced Cursor Types

ADO provides several cursor types, each designed for different application requirements.

Forward-Only Cursor

adOpenForwardOnly

This is the fastest cursor type.

Features:

  • Moves only forward.

  • Cannot return to previous records.

  • Consumes minimal resources.

  • Best for reports and data reading.

Example:

Do Until rs.EOF
    Response.Write rs("EmployeeName")
    rs.MoveNext
Loop

Use case:

Generating employee reports.

Static Cursor

adOpenStatic

A static cursor creates a snapshot of the data.

Features:

  • Does not show new records added later.

  • Deleted records remain visible until refreshed.

  • Supports bookmarks.

  • Allows backward and forward movement.

Example:

Employee records are loaded at 10:00 AM.

Another user adds five employees at 10:15 AM.

The static cursor still displays only the original records until the Recordset is reopened.

Use case:

Generating monthly reports where data consistency is important.

Keyset Cursor

adOpenKeyset

The keyset cursor stores only the keys of retrieved records.

Features:

  • Existing records can be updated.

  • Deleted records become unavailable.

  • Newly inserted records are not visible.

  • Moderate performance.

Example:

Employee IDs remain fixed.

If another user updates an employee's salary, the changes become visible.

If another user adds a new employee, the new record does not appear.

Use case:

Applications requiring updated information without showing newly added records.

Dynamic Cursor

adOpenDynamic

The dynamic cursor provides a live view of the database.

Features:

  • Shows inserted records.

  • Shows deleted records.

  • Shows updated records.

  • Reflects all database changes immediately.

  • Highest resource usage.

Example:

Five users edit the same table.

The application instantly reflects all modifications.

Use case:

Real-time monitoring systems.

Cursor Navigation Techniques

Advanced cursor services allow flexible movement within a Recordset.

Move to first record

rs.MoveFirst

Move to last record

rs.MoveLast

Move to next record

rs.MoveNext

Move to previous record

rs.MovePrevious

Move several records

rs.Move 5

Move backward

rs.Move -3

These navigation methods allow efficient browsing of data.

Using Bookmarks

Bookmarks allow saving the current record position.

Example:

Dim mark

mark = rs.Bookmark

Later:

rs.Bookmark = mark

Benefits:

  • Return to important records instantly.

  • Useful in large Recordsets.

  • Eliminates repeated searching.

Example:

A customer service application remembers the selected customer while browsing other records.

Record Searching

ADO supports searching within a Recordset.

Example:

rs.Find "EmployeeID = 105"

Benefits:

  • Quickly locate records.

  • Faster than manually looping through all records.

  • Improves user experience.

Record Filtering

Filtering displays only records matching specified criteria.

Example:

rs.Filter = "Department='Sales'"

Only Sales department employees are displayed.

Another example:

rs.Filter = "Salary > 50000"

Benefits:

  • Reduces visible records.

  • Makes searching easier.

  • Improves application responsiveness.

Record Sorting

Client-side cursors support sorting.

Example:

rs.Sort = "EmployeeName ASC"

Descending order:

rs.Sort = "Salary DESC"

Benefits:

  • No additional database query.

  • Faster data organization.

  • Supports multiple sorting operations.

Record Caching

Cursor services temporarily store retrieved records in memory.

Advantages:

  • Reduces repeated database requests.

  • Improves application speed.

  • Decreases network traffic.

  • Enhances user experience.

Example:

Instead of requesting customer data repeatedly, the application retrieves it once and reuses the cached Recordset.

Batch Processing

Cursor services support batch updates.

Example:

rs.LockType = adLockBatchOptimistic

After multiple modifications:

rs.UpdateBatch

Benefits:

  • Reduces network communication.

  • Improves performance.

  • Updates many records together.

Cursor Lock Types

Cursor services work with various locking methods.

Read Only

adLockReadOnly

Records cannot be modified.

Best for reports.

Optimistic Lock

adLockOptimistic

Locks a record only while updating it.

Best for applications with multiple users.

Pessimistic Lock

adLockPessimistic

Locks the record immediately after editing begins.

Prevents other users from making changes.

Suitable for critical transactions.

Batch Optimistic Lock

adLockBatchOptimistic

Allows offline editing and later synchronization.

Ideal for disconnected Recordsets.

Performance Optimization Techniques

Advanced cursor services improve performance by:

  • Fetching only required records.

  • Using client-side cursors when appropriate.

  • Avoiding unnecessary database connections.

  • Using Forward-Only cursors for read-only applications.

  • Using batch updates.

  • Caching frequently accessed data.

  • Filtering locally instead of repeatedly querying the database.

Real-World Applications

Banking Systems

Customer records are loaded into a client-side cursor. Bank employees search, filter, and sort customer information quickly without constantly accessing the server.

Hospital Management

Doctors retrieve patient records, review them offline, and synchronize updates later. Cursor services support efficient navigation through medical histories.

Inventory Management

Warehouse staff use Recordsets to sort products by category, search for item codes, and update stock levels in batches, reducing server load.

Human Resource Management

HR departments use bookmarks to return to employee records, filter employees by department, and sort payroll information efficiently.

Sales Applications

Sales teams retrieve customer lists once, then filter by region, sort by revenue, and search for specific accounts without repeatedly querying the database.

Advantages of Advanced Cursor Service Techniques

  • Improves application performance.

  • Reduces database server workload.

  • Supports offline data access.

  • Enables efficient sorting and filtering.

  • Provides flexible record navigation.

  • Supports bookmarks for quick access.

  • Allows batch processing of updates.

  • Enhances scalability in multi-user applications.

  • Reduces network traffic.

  • Improves overall user experience.

Limitations

  • Client-side cursors consume more memory.

  • Dynamic cursors require more server resources.

  • Large Recordsets may slow application performance.

  • Offline data may become outdated if the database changes before synchronization.

  • Incorrect cursor selection can negatively affect application efficiency.

Best Practices

  • Use adOpenForwardOnly for read-only operations to maximize performance.

  • Use adOpenStatic when a consistent snapshot of data is required.

  • Use adOpenDynamic only when real-time updates are essential.

  • Prefer client-side cursors (adUseClient) for sorting, filtering, and disconnected operations.

  • Use server-side cursors (adUseServer) when working with very large datasets that should not be fully loaded into client memory.

  • Combine appropriate cursor types with suitable lock types to balance performance, concurrency, and data integrity.

  • Retrieve only the required columns and records to reduce memory usage and improve response times.

Conclusion

Advanced Cursor Service Techniques in ADO provide developers with powerful tools for navigating, managing, and optimizing database interactions. By understanding cursor types, cursor locations, navigation methods, filtering, sorting, bookmarks, caching, and batch processing, developers can create applications that are faster, more scalable, and easier to maintain. Selecting the right cursor strategy for each scenario ensures efficient resource utilization while delivering a responsive and reliable user experience.