Clone Method

Applies To Dynaset-Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Creates a duplicate Recordset object that refers to the original Recordset object.

Syntax

Set duplicate = original.Clone

The Clone method syntax has these parts.

Part	Description

duplicate	An object variable identifying the duplicate Recordset object you're creating.
original	An object variable identifying the Recordset object you want to duplicate.

Remarks Use the Clone method to create multiple, duplicate Recordset objects. Each Recordset can have its own current record. Using Clone by itself doesn't change the data in the objects or in their underlying structures. When you use the Clone method, you can share bookmarks between two or more Recordset objects because their bookmarks are interchangeable.

You can use the Clone method when you want to perform an operation on a Recordset that requires multiple current records. This is faster and more efficient than opening a second Recordset. When you create a Recordset with the Clone method, it initially lacks a current record. To make a record current before you use the Recordset clone, you must set the Bookmark property or use one of the Move methods, one of the Find methods, or the Seek method.

Using the Close method on either the original or duplicate object doesn't affect the other object. For example, using Close on the original Recordset doesn't close the clone.

Notes

Closing a clone Recordset within a pending transaction will cause an implicit Rollback operation.
When you clone a table-type Recordset object in a Microsoft Jet workspace, the Index property setting is not cloned on the new copy of the Recordset. You must copy the Index property setting manually.
You can use the Clone method with forward-only–type Recordset objects only in an ODBCDirect workspace.

See Also

Bookmark property.

Specifics (Microsoft Access)

If you use the Bookmark property of a Recordset object in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

Example

This example uses the Clone method to create copies of a Recordset and then lets the user position the record pointer of each copy independently.

Sub CloneX()

    Dim dbsNorthwind As Database
    Dim arstProducts(1 To 3) As Recordset
    Dim intLoop As Integer
    Dim strMessage As String
    Dim strFind As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' If the following SQL statement will be used often,
    ' creating a permanent QueryDef will result in better
    ' performance.
    Set arstProducts(1) = dbsNorthwind.OpenRecordset( _
        "SELECT ProductName FROM Products " & _
        "ORDER BY ProductName", dbOpenSnapshot)

    ' Create two clones of the original Recordset.
    Set arstProducts(2) = arstProducts(1).Clone
    Set arstProducts(3) = arstProducts(1).Clone

    Do While True

        ' Loop through the array so that on each pass, the
        ' user is searching a different copy of the same
        ' Recordset.
        For intLoop = 1 To 3

            ' Ask for search string while showing where the
            ' current record pointer is for each Recordset.
            strMessage = _
                "Recordsets from Products table:" & vbCr & _
                "  1 - Original - Record pointer at " & _
                arstProducts(1)!ProductName & vbCr & _
                "  2 - Clone - Record pointer at " & _
                arstProducts(2)!ProductName & vbCr & _
                "  3 - Clone - Record pointer at " & _
                arstProducts(3)!ProductName & vbCr & _
                "Enter search string for #" & intLoop & ":"
            strFind = Trim(InputBox(strMessage))
            If strFind = "" Then Exit Do

            ' Find the search string; if there's no match, jump
            ' to the last record.
            With arstProducts(intLoop)
                .FindFirst "ProductName >= '" & strFind & "'"
                If .NoMatch Then .MoveLast
            End With

        Next intLoop

    Loop

    arstProducts(1).Close
    arstProducts(2).Close
    arstProducts(3).Close
    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a Recordset object by using an SQL statement on an Employees table and then uses the Clone method to create a clone of the Recordset object so that bookmarks can be shared between the two objects. This technique is especially useful when you have to compare the results of a query from more than one point at the same time.

The function reads and stores in a string variable the value of the Bookmark property for the current record, in this case the second record in the original Recordset object. The Bookmark property of the duplicate Recordset object is set to this string, making the second record the current record in this Recordset object as well. The current values of the LastName field for both recordsets are identical, as you can see once they are printed in the Debug window.

Sub CreateClone()
    Dim dbs As Database
    Dim rstEmployees As Recordset, rstDuplicate As Recordset
    Dim fldName As Field, varBook As Variant

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open dynaset-type Recordset object.
    Set rstEmployees = dbs.OpenRecordset("SELECT * FROM Employees " _
        & " ORDER BY LastName")
    ' Clone Recordset object.
    Set rstDuplicate = rstEmployees.Clone
    Set fldName = rstEmployees.Fields!LastName
    ' Set current record.
    rstEmployees.MoveFirst
    ' Move to second record.
    rstEmployees.MoveNext
    ' Get Bookmark property value and print current field value.
    If rstEmployees.Bookmarkable Then
        varBook = rstEmployees.Bookmark
        Debug.Print fldName.value
    Else
        ' If Recordset object doesn't support bookmarks, exit procedure.
        ExitCreateClone
    End If
    ' Set Bookmark property of clone to obtained value.
    rstDuplicate.Bookmark = varBook
    Debug.Print fldName.value

ExitCreateClone:
    rstEmployees.Close
    rstDuplicate.Close
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example displays a custom dialog box that contains the lists of data from the CONTACTS and CUSTMR_ID fields in the Customer recordset of the Nwindex.mdb database.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database
Dim rs1 As Recordset, rs2 As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs1 = db.OpenRecordset("SELECT * FROM Customer" _
    & " WHERE [REGION] = 'WA' ORDER BY [CUSTMR_ID];")
Set theDialog = DialogSheets.Add
Set list1 = theDialog.ListBoxes.Add(78, 42, 84, 80)
Set list2 = theDialog.ListBoxes.Add(183, 42, 84, 80)
Set rs2 = rs1.Clone()
rs2.MoveFirst
Do Until rs1.EOF
    list1.AddItem (rs1.Fields("CONTACT").Value)
    rs1.MoveNext
Loop
Do Until rs2.EOF
    list2.AddItem (rs2.Fields("CUSTMR_ID").Value)
    rs2.MoveNext
Loop
rs1.Close
rs2.Close
db.Close