ADO - ADO Recordset CompareBookmarks Method for Comparing Record Positions

 
 

1. Introduction

In ActiveX Data Objects (ADO), a Recordset represents a collection of records retrieved from a database. Each record in a Recordset contains information arranged in fields and can be accessed, modified, or deleted using ADO methods and properties.

When working with a Recordset, it is sometimes necessary to identify whether two bookmarks refer to the same record or different records. A bookmark is a value that identifies a particular record within a Recordset. ADO provides the CompareBookmarks method to compare two bookmark values and determine their relationship.

The CompareBookmarks method is particularly useful when an application needs to track record positions, compare records, or determine whether two references point to the same database record.

2. What Is the CompareBookmarks Method?

The CompareBookmarks method is a built-in method of the ADO Recordset object. It compares two bookmark values and returns a result indicating whether the bookmarks refer to the same record or occupy different positions.

A bookmark does not necessarily represent the actual primary key of a database table. Instead, it is an ADO-generated reference used to identify a record's position within a particular Recordset.

For example, consider an employee database containing the following records:

Employee ID

Employee Name

Department

101

John

HR

102

David

Finance

103

Sarah

IT

104

Michael

Sales

Suppose an application stores the bookmark of the employee named David and later retrieves another bookmark for the same employee. The CompareBookmarks method can determine whether both bookmarks refer to the same record.

This helps applications avoid unnecessary record processing and makes record navigation easier.

3. Syntax of the CompareBookmarks Method

The general syntax of the CompareBookmarks method is:

result = Recordset.CompareBookmarks(Bookmark1, Bookmark2)

The method accepts two bookmark values as arguments.

Parameters:

  • Bookmark1: Represents the first bookmark value that needs to be compared.

  • Bookmark2: Represents the second bookmark value that needs to be compared.

Return Value:

The method returns a value indicating the relationship between the two bookmarks.

Return value

Meaning

adCompareLessThan (-1)

The first bookmark precedes the second bookmark.

adCompareEqual (0)

Both bookmarks refer to the same record.

adCompareGreaterThan (1)

The first bookmark follows the second bookmark.

adCompareNotEqual (2)

The bookmarks cannot be considered equal, but their relative order cannot be determined.

adCompareNotComparable (4)

The bookmarks cannot be compared.

The returned values help developers understand the relationship between record positions.

4. How Does CompareBookmarks Work?

The CompareBookmarks method works by accepting two bookmark values from a Recordset and checking their relationship.

The process generally involves the following steps:

  1. An application establishes a connection to a database using an ADO Connection object.

  2. It executes a query to retrieve records from the database.

  3. The query results are stored in a Recordset object.

  4. The application moves to a particular record and retrieves its bookmark.

  5. It moves to another record or retrieves another previously stored bookmark.

  6. The CompareBookmarks method compares the two bookmark values.

  7. The method returns a comparison result based on the relationship between the bookmarks.

For example, if both bookmarks identify the same employee record, the method returns adCompareEqual.

If the bookmarks identify different records and their relative order can be determined, the method returns adCompareLessThan or adCompareGreaterThan.

5. Example of CompareBookmarks in VBScript

The following example demonstrates how to use the CompareBookmarks method to compare two records in an ADO Recordset.

Dim conn
Dim rs
Dim bookmark1
Dim bookmark2
Dim result

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Employee.accdb"

rs.Open "SELECT * FROM Employees ORDER BY EmployeeID", conn, 3, 1

' Move to the first record
rs.MoveFirst
bookmark1 = rs.Bookmark

' Move to the second record
rs.MoveNext
bookmark2 = rs.Bookmark

' Compare the two bookmarks
result = rs.CompareBookmarks(bookmark1, bookmark2)

If result = 0 Then
    MsgBox "Both bookmarks refer to the same record."
ElseIf result = -1 Then
    MsgBox "The first bookmark comes before the second bookmark."
ElseIf result = 1 Then
    MsgBox "The first bookmark comes after the second bookmark."
Else
    MsgBox "The bookmarks are not equal or cannot be compared."
End If

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

Explanation of the program:

  • First, the program creates ADO Connection and Recordset objects.

  • The Connection object establishes a connection to the Microsoft Access database.

  • The Recordset object retrieves employee information from the Employees table.

  • The MoveFirst method moves the record pointer to the first record.

  • The Bookmark property stores the bookmark of the first record.

  • The MoveNext method moves the record pointer to the next record.

  • The second bookmark is stored in another variable.

  • The CompareBookmarks method compares both bookmark values.

  • Finally, the program displays a message based on the comparison result.

The example assumes that the database file and table exist and that the required Microsoft Access database provider is installed.

6. Requirements and Limitations of CompareBookmarks

Although CompareBookmarks is useful for record comparison, developers should understand its requirements and limitations.

1. Bookmark Support

The Recordset must support bookmarks. Some Recordset types and data providers do not support bookmark functionality.

2. Same Recordset

The two bookmarks should belong to the same Recordset. Comparing bookmarks from unrelated Recordsets is not a reliable way to identify matching database records.

3. Recordset Changes

When records are deleted or the Recordset is modified, previously stored bookmarks may no longer be valid. Developers should retrieve and use bookmarks carefully.

4. Provider Compatibility

The behavior and availability of bookmark comparison depend on the database provider and the Recordset implementation.

5. Not a Primary Key Comparison

A bookmark is not necessarily a permanent database identifier. If an application needs to compare records across different database queries or connections, comparing primary key values is generally more appropriate.

7. Advantages of the CompareBookmarks Method

The CompareBookmarks method provides several benefits when developing database applications.

  • Accurate Record Comparison: Helps determine whether two bookmark references identify the same record.

  • Improved Record Navigation: Makes it easier to work with record positions within a Recordset.

  • Reduced Unnecessary Processing: Applications can avoid repeating operations when two bookmarks refer to the same record.

  • Simplified Database Programming: Provides a built-in approach to comparing bookmark values instead of manually managing record positions.

  • Useful for Record Tracking: Helps applications maintain references to previously accessed records.

8. Practical Applications of CompareBookmarks

The CompareBookmarks method can be useful in several real-world database applications.

Employee Management Systems: An employee management application can compare bookmarks to determine whether two references point to the same employee record while navigating employee information.

Inventory Management Systems: Inventory applications can use bookmark comparison when tracking the current position of product records within a Recordset.

Customer Relationship Management Systems: CRM applications can use bookmarks to track customer records during navigation and compare previously stored record references.

Database Reporting Applications: Reporting software can use bookmark comparison to identify whether two record references point to the same position while processing query results.

Data Navigation Applications: Applications that allow users to move between records can use bookmarks to maintain and compare record positions.

9. Difference Between Bookmark Property and CompareBookmarks Method

The Bookmark property and CompareBookmarks method are related, but they perform different functions.

Bookmark Property

CompareBookmarks Method

Identifies a particular record in a Recordset.

Compares two bookmark values.

Retrieves or sets a record position.

Determines the relationship between two record positions.

Uses a bookmark value.

Accepts two bookmark values.

Helps return to a previously accessed record.

Helps determine whether two bookmarks refer to the same or different records.

Both features are useful for managing record positions and improving navigation within ADO applications.

10. Conclusion

The ADO CompareBookmarks method is an important feature for applications that need to compare record positions within a Recordset. It accepts two bookmark values and returns a result indicating whether they refer to the same record, whether one precedes the other, or whether they cannot be compared.

By using this method, developers can improve record navigation, manage stored record references, and simplify database operations. Although bookmarks are useful for identifying records within a Recordset, they should not be treated as permanent database identifiers. Understanding the CompareBookmarks method helps developers build more organized and efficient applications using ActiveX Data Objects.