ADO - ADO Clone Method for Recordset Duplication

The Clone method in ActiveX Data Objects (ADO) is used to create a duplicate Recordset object from an existing Recordset. The cloned Recordset contains the same records as the original Recordset and generally has the same structure, but it can be used independently for navigation and certain operations. This is particularly useful when an application needs to work with the same set of data in more than one way at the same time.

What Is the ADO Clone Method?

In ADO, a Recordset represents a collection of records retrieved from a data source. When an application creates a clone of a Recordset, ADO creates another Recordset object based on the original one.

The basic syntax is:

Set cloneRecordset = originalRecordset.Clone

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

The important point is that cloning does not mean that ADO executes the original database query again. Instead, the clone provides another view of the same underlying Recordset data.

Why Use the Clone Method?

The main advantage of Clone is that it allows an application to maintain different positions within the same set of records.

For example, suppose an application displays customer records in one part of the interface while another operation needs to search through those same records. Moving the current record of the original Recordset could interfere with the application's display or other processing.

A clone can solve this problem:

Set customerClone = customerRecordset.Clone

The original Recordset can remain positioned on one record while the cloned Recordset can be moved independently.

This makes Clone particularly useful when an application needs multiple navigation points over the same Recordset.

Independent Record Navigation

One of the most important characteristics of a cloned Recordset is that its current record position can be changed independently from the original Recordset.

Consider a Recordset containing:

CustomerID    CustomerName
101           Ravi
102           Anita
103           Kumar
104           Priya
105           Sanjay

Suppose the original Recordset is currently positioned on Ravi.

A clone can be created:

Set customerClone = customerRecordset.Clone

The cloned Recordset can then move to Priya without changing the current position of the original Recordset.

For example:

customerClone.MoveFirst
customerClone.Move 3

The original Recordset and the clone can therefore be used for separate navigation tasks.

Clone Does Not Mean a Completely Separate Database Copy

It is important to understand that the Clone method does not create a completely independent copy of the underlying database data.

The clone is associated with the same underlying Recordset data. Therefore, applications should not interpret Clone as a method for duplicating database records or creating a new database table.

For example:

Set rsClone = rs.Clone

does not mean that ADO creates another set of database rows.

Instead, it creates another Recordset object that provides another way to work with the same Recordset data.

This distinction is important because Recordset cloning and database data duplication are two different concepts.

Example of Using Clone

Consider the following Visual Basic example:

Dim rs As ADODB.Recordset
Dim rsClone As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT CustomerID, CustomerName FROM Customers", _
        connectionObject, _
        adOpenStatic, _
        adLockReadOnly

Set rsClone = rs.Clone

The original Recordset is opened first. The Clone method is then used to create another Recordset object.

The application can now use:

rs

for its primary processing and:

rsClone

for another navigation task.

For example:

rs.MoveFirst
rsClone.MoveLast

The two objects can maintain different current positions.

Clone and Recordset Editing

A cloned Recordset can also be useful when working with editable Recordsets, but developers must understand the interaction between the clone and the underlying data.

For example:

Set rsClone = rs.Clone

If the Recordset supports editing, changes made through one Recordset may be reflected through the shared underlying Recordset data, depending on the Recordset type, provider, cursor configuration, and locking behavior.

Therefore, Clone should not automatically be treated as a completely isolated editing environment.

Its strongest and most straightforward use is providing separate navigation and views of the same Recordset.

Clone and Searching

Another useful application of Clone is searching while preserving the original Recordset's position.

Suppose the main application is currently displaying customer number 103. The application needs to locate another customer without disturbing the current position.

Instead of searching directly through the original Recordset, it can create a clone:

Set searchRS = rs.Clone

The search can then be performed using the cloned Recordset.

This approach is useful in applications where the original Recordset represents the user's current view and should remain unchanged while another operation is performed.

Clone and Bookmarks

The Clone method works particularly well with the Bookmark property.

A bookmark identifies the current position of a Recordset. An application can save the position of the original Recordset and use a clone for another operation.

For example:

Dim savedPosition As Variant

savedPosition = rs.Bookmark

Set rsClone = rs.Clone

The clone can then be moved around without requiring the application to lose track of the original position.

This can be useful in user interfaces where a Recordset is being displayed while background operations need to inspect the same data.

Important Limitations

The Clone method has several limitations that developers should consider.

First, not every Recordset configuration or provider supports every operation in the same way. The behavior of cloning can depend on the underlying OLE DB provider and the type of cursor being used.

Second, a clone is not a permanent backup of the Recordset. If the underlying data changes, the relationship between the original and cloned Recordsets can depend on the cursor type and provider.

Third, Clone does not duplicate database records. If the goal is to create new database records, an application must explicitly perform an insert or another appropriate database operation.

Fourth, developers should not assume that every property and capability behaves independently after cloning. The cloned Recordset is associated with the original Recordset's underlying data and cursor environment.

Clone Method vs. Reopening the Query

There is an important difference between cloning a Recordset and executing the original query again.

With Clone:

Set rsClone = rs.Clone

ADO creates another Recordset object based on the existing Recordset.

With a new query:

Set rs2 = New ADODB.Recordset
rs2.Open "SELECT CustomerID, CustomerName FROM Customers", connectionObject

the application executes another database operation.

Therefore, Clone can be useful when an application already has the required data and simply needs another Recordset object for navigation or processing.

Practical Applications

The Clone method can be useful in several types of applications.

In a customer management system, the main Recordset can display customer information while a cloned Recordset searches for another customer.

In a reporting application, one Recordset can remain positioned at the current record while another is used to inspect or navigate through the same data.

In a data-entry application, cloning can help maintain separate navigation contexts when several operations need access to the same Recordset.

In a desktop database application, a clone can be used when the interface needs to preserve the user's current record while another operation navigates through the Recordset.

Conclusion

The ADO Clone method provides a convenient way to create another Recordset object based on an existing Recordset. Its major benefit is allowing applications to maintain separate navigation positions while working with the same underlying Recordset data. It is especially useful for searching, browsing, comparing, and performing secondary processing without unnecessarily disturbing the current position of the main Recordset.

The key concept to remember is that Clone creates another Recordset view; it does not create a separate copy of the database records. This makes it a useful tool for managing multiple navigation contexts within an ADO application.