Export (0) Print
Expand All

Recordset.Clone Method (DAO)

Last modified: June 29, 2011

Applies to: Access 2013 | Office 2013

In this article
Syntax
Remarks
Example

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

expression .Clone

expression A variable that represents a Recordset object.

Return Value

Recordset

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.

Note Note
  • 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 Access workspace, the Index property setting is not cloned on the new copy of the recordset. You must copy the Index property setting manually.

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 

Show:
© 2014 Microsoft