Cloning a Recordset

Sometimes you may want to manipulate a recordset without losing the current position in the recordset. You can do this by cloning your original recordset. Use the ADO Clone method to create a recordset that is a copy of another recordset. You can create a recordset clone like this:

Dim rstOrg As ADODB.Recordset ' your original recordset

Dim rstClone As ADODB.Recordset ' cloned recordset

Set rstCone = myOrgRst.Clone

As you can see from the assignment statement above, a clone is an object variable containing a reference to the original recordset. After you've used the Clone method, you end up with two copies of the recordset that contain the same records but can be filtered and manipulated separately. You can create more than one clone of the original recordset.

Use the Clone method when you want to perform an operation on a recordset that requires multiple current records. The Clone object and the original Recordset object each have their own current records; therefore, the record pointers in the original and cloned recordsets can move independently of one another. And, because the clone points to the same set of data as the original, any changes made using either the original recordset or any of its clones will be visible in the original and its clones. However, the original recordset and its clones can get out of sync if you requery the original recordset against the database.

When you close the original recordset, the clones remain open until you close them. Closing any of the clones does not close the original recordset.

Because the Clone method does not create another copy of the data (it only points to the data), cloning a recordset is faster and more efficient than opening a second recordset based on the same criteria. A recordset created by a method

Part II

other than cloning will have a different set of bookmarks than the original recordset, even when it is based on the same SQL statement.

You can make a clone read-only by using an optional parameter like this:

Set rstCone = myOrgRst.Clone(adLockReadOnly)

It's worth mentioning that you can only clone bookmarkable recordsets. Use the Recordset object's Supports method to find out if the recordset supports bookmarks (see the section called "Using Bookmarks" in Chapter 13). If you try to clone a non-bookmarkable recordset, you will receive a run-time error.

The clone and the original recordset have the same bookmarks, which you can share. A bookmark reference from one Recordset object refers to the same record in any of its clones.

In Access, the Combo Box Wizard uses the Clone method in the combo box's AfterUpdate event. You will examine this code while building Custom Project 16-3. This project demonstrates how the Clone method can be used to create a single form for displaying the current and previous record side by side (see Figure 16-4).

0 0

Post a comment