Clone dynaset recordsets instead of opening new ones

The Clone method is a little used method on a Recordset object. This method is an efficient and fast way of creating a recordset that is a "copy" of another recordset. It is much more efficient than rerunning a query, or even reopening a table-type recordset. As a general rule, cloning a recordset is a low cost operation.

Some examples of where you might use clones include:

• If you start editing a record, but haven't yet done an .Update, a clone can still retrieve the underlying field values, even after you've changed some of them. This can be useful if the .Update fails because someone else has modified the record and you wish to present the user with the original data. This code snippet shows how you might do this:

  
  

          Dim rst1 As Recordset, rst2 As Recordset
          Set rst1 = dbsCurrent.OpenRecordset("Table1")
          Set rstClone = rst1.Clone()
          
          Debug.Print rst1!name
          rst1.Edit
          rst1!name = "Smith"
          Debug.Print rst1!name
          Debug.Print rstClone!name
          ...

  The code above will print out the original value of Name, followed by "Smith," followed by the original name, because the recordset clone, rstClone, has access to the stored name until the Update method is called.

• If you have a block of records that are, say, displayed to the user, then you might maintain currency of one recordset at the end of the block, while using a clone to move within the block. This is especially useful when combined with the caching methods discussed further in this paper.