ADO - ADO Recordset Clone Method

The Clone method in ADO is used to create a duplicate of an existing Recordset object. The cloned Recordset contains the same records and fields as the original Recordset, but it has its own current record position. This is useful when an application needs to work with the same set of data in more than one way at the same time. For example, one Recordset can remain positioned on a particular record while another Recordset navigates through the same records independently.

Syntax

Set cloneRecordset = originalRecordset.Clone

Here, originalRecordset is the existing Recordset and cloneRecordset receives the newly created copy.

A clone does not normally mean that ADO creates an entirely separate copy of all the underlying database data. Instead, the cloned Recordset provides another view or cursor over the same underlying Recordset data. Therefore, changes made through one Recordset can potentially be visible through the other, depending on the cursor type, provider, locking mechanism, and how the Recordset was opened.

Why Use the Clone Method?

The main purpose of Clone is to allow an application to have multiple independent positions within the same Recordset.

Suppose an application has a Recordset containing 1,000 customer records. The main Recordset is currently positioned on customer number 500. If the application needs to search through the records from the beginning without disturbing the current position, it can create a clone.

Set rsClone = rsCustomers.Clone
rsClone.MoveFirst

The original rsCustomers can remain positioned on customer 500, while rsClone can start navigating from the first record.

This is particularly useful when an application needs to perform temporary navigation, searching, comparison, or secondary processing without changing the position of the original Recordset.

Example

Consider the following example:

Dim rsCustomers As ADODB.Recordset
Dim rsClone As ADODB.Recordset

Set rsCustomers = New ADODB.Recordset

rsCustomers.Open "SELECT CustomerID, CustomerName FROM Customers", _
                connectionObject, _
                adOpenStatic, _
                adLockOptimistic

Set rsClone = rsCustomers.Clone

rsClone.MoveFirst

Do Until rsClone.EOF
    Debug.Print rsClone.Fields("CustomerName").Value
    rsClone.MoveNext
Loop

In this example, rsCustomers contains customer information. The Clone method creates rsClone, which can be used to navigate through the customer records.

The original Recordset remains available for other operations.

Independent Record Positions

One of the most important characteristics of Clone is that the original and cloned Recordsets can have different current positions.

For example:

Set rsClone = rsCustomers.Clone

rsCustomers.MoveFirst
rsCustomers.MoveNext
rsCustomers.MoveNext

rsClone.MoveFirst

After these operations, rsCustomers and rsClone do not necessarily point to the same record. The original Recordset has moved forward, while the clone has moved to the first record.

This independent positioning is one of the main reasons to use the Clone method.

Clone and Bookmarks

The Clone method is particularly useful together with the Bookmark property.

A bookmark identifies a particular record in a Recordset. An application can save the position of a record in one Recordset and use the corresponding bookmark in another compatible Recordset.

For example:

Dim savedPosition As Variant

savedPosition = rsCustomers.Bookmark

Set rsClone = rsCustomers.Clone

rsClone.Bookmark = savedPosition

This allows the application to return to a particular record without navigating through the entire Recordset again.

Clone for Searching

Another practical use of Clone is performing searches without disturbing the user's current position.

Suppose a user is viewing customer number 100. The application needs to search for a particular customer name. Instead of moving the original Recordset, it can create a clone:

Set rsSearch = rsCustomers.Clone

rsSearch.MoveFirst

Do Until rsSearch.EOF
    If rsSearch.Fields("CustomerName").Value = "John Smith" Then
        Debug.Print "Customer Found"
        Exit Do
    End If

    rsSearch.MoveNext
Loop

The search operation takes place on rsSearch, leaving rsCustomers available for the main application.

Clone and Recordset Changes

A clone is associated with the original Recordset's data. Consequently, developers should understand that cloning does not necessarily provide a completely independent copy of the database contents.

For example, if a Recordset supports updating and a record is modified, the visibility of that modification through another clone depends on the provider and cursor configuration.

Therefore, the following two concepts should not be confused:

Clone
  |
  +-- Provides another Recordset view/cursor
  +-- Independent record position
  +-- Can share underlying Recordset data
  +-- Does not mean an independent database copy

If a completely separate collection of data is required, the application may need to copy the values into another structure, such as an array or another data container, rather than simply cloning the Recordset.

Clone with Different Cursor Types

The behavior of a cloned Recordset depends partly on the cursor used by the original Recordset.

For example, a static cursor may provide a snapshot-like view of records, while other cursor types can have different behavior regarding updates and visibility.

A developer should therefore consider:

  • Cursor type

  • Lock type

  • Database provider

  • Whether the Recordset is updateable

  • Whether the Recordset is client-side or server-side

  • Whether the provider supports the required cloning behavior

These factors can affect how changes are reflected between the original Recordset and its clone.

Checking for an Empty Recordset

Before using MoveFirst, it is important to verify that the Recordset actually contains records.

For example:

Set rsClone = rsCustomers.Clone

If Not rsClone.EOF Then
    rsClone.MoveFirst

    Do Until rsClone.EOF
        Debug.Print rsClone.Fields("CustomerName").Value
        rsClone.MoveNext
    Loop
End If

This prevents navigation operations from being performed on an empty Recordset.

Closing the Clone

When the cloned Recordset is no longer required, it should be closed and released.

rsClone.Close
Set rsClone = Nothing

The original Recordset can continue to be used:

rsCustomers.Close
Set rsCustomers = Nothing

Closing the clone does not necessarily mean that the original Recordset is closed. They are separate Recordset objects, although they may share underlying resources.

Advantages of the Clone Method

The Clone method provides several advantages:

  1. It allows multiple independent record positions.

  2. It avoids repeatedly executing the same database query for simple navigation tasks.

  3. It is useful for temporary searches and comparisons.

  4. It can be combined with bookmarks for efficient record positioning.

  5. It allows one Recordset to remain at the user's current record while another processes the same data.

  6. It can simplify applications that need multiple views of the same Recordset.

Limitations

The Clone method also has limitations. A clone is not necessarily a completely independent copy of the underlying data. Its behavior depends on the ADO provider and cursor configuration. Not every Recordset configuration provides identical cloning capabilities, and changes made through one Recordset may have different visibility characteristics in the clone.

For large datasets, developers should also avoid assuming that cloning automatically eliminates memory or database-resource costs. The actual resource usage depends on the cursor location, provider, and Recordset configuration.

Clone vs. Creating a New Recordset

These two approaches are different:

Set rsClone = rsOriginal.Clone

creates another Recordset based on the existing Recordset.

Whereas:

Set rsNew = New ADODB.Recordset

rsNew.Open "SELECT * FROM Customers", connectionObject

creates a new Recordset by executing another query.

The Clone method can therefore be useful when the application already has the required data and simply needs another independent navigation position.

Conclusion

The ADO Recordset Clone method creates another Recordset object based on an existing Recordset and is mainly useful when an application needs to work with the same data through multiple independent record positions. It is especially valuable for searching, navigation, comparison, bookmarks, and temporary processing without disturbing the position of the original Recordset.

The key point to remember is that Clone creates another Recordset view/cursor, not necessarily a completely separate copy of the underlying database data. Its exact behavior depends on the Recordset's cursor, locking configuration, provider, and other ADO settings.