Chinese - Simplified   Dutch   English - United States   French   German   Portuguese - Iberian   Spanish  


Usenet Posting #22 - Trigeminal Software, Inc. (English)

Subject: INFO: Form Recordsets vs. Form RecordsetClones in Access 2000
(Originally posted 1/22/00)

These two objects have created a fair amount of confusion, so I thought it might be a good idea to try to explain some of the issues behind them.

First, there is the RecordsetClone. This object has been around since Access 2.0, and it allowed you to get a DAO recordset that was a clone of the recordset that Access used for the form itself. Since it was a clone, you were allowed to perform operations in the clone and expect those changes to be seen in the form itself; you could even navigate using the clone (perhaps with a FindFirst call, for example) and by synching the form's bookmark with the RecordsetClone's, you could cause the form's record to change.

Throughout Access 2.0, 95, and 97, the form's actual recordset was never available for use, however.

Now enter Access 2000, where forms do indeed have a Recordset property, which will accept either a DAO or an ADO recordset. Rather than being a clone, this recordset would be a true pointer to the exact same recordset that the form itself uses (thus no synchronization of bookmarks would be needed as it sometimes is with the RecordsetClone). There are some important rules to remember about the interaction of these two properties, the file type, and other issues, which I will try and list below:

  • If you do not set the form's Recordset property and the form is in an .MDB file, then the Recordset and RecordsetClone properties will return a DAO 3.6 Recordset.
  • If you do not set the form's Recordset property and the form is in an .ADP file, then the Recordset and RecordsetClone properties will return an ADO 2.1 Recordset based on the Jet 4.0 OLE DB Provider.
  • If you do set the form's Recordset property, then subsequent calls to both the Recordset and RecordsetClone properties will return the exact same type of recordset as the one you initially set the property to.
  • The Recordset property can be set to a DAO 3.6 Recordset or an ADO 2.1 Recordset based on either the 3.5x Jet OLE DB provider or the Jet 4.0 OLE DB Provider. This is true regardless of the project type.
  • At present, the ADO recordsets mentioned above will cause the form to be read-only.
  • In an ADP file, every time you retrieve the RecordsetClone of a form, you will be given a new instance of a cloned recordset. This is not the case in an .MDB file, where you always have in the past and continue in the present day to use the same recordset and cursor. Why does this matter? Well, consider code such as the following, which the Access 95/97 Listbox/Combobox wizard would offer to generate for a control that is intended to navigate to new recordsets on the form:

    Me.RecordSetClone.Find "[id] = " & Me.YourComboBox.Value
    Me.Bookmark = Me.RecordsetClone.Bookmark
    This sort of code (allowing for syntax differences, since the DAO method would usually be FindFirst) worked fine in all prior versions of Access and continues to work in .MDBs in Access 2000. In ADPs, however, this code will not work properly since each call to the RecordsetClone property returns a new cursor, and thus the first "Find" operation will not have anything to do with the RecordsetClone returned by the call in the second line. For Access 2000, this code was changed to the following to allow for this difference:

    Dim rs As ADODB.Recordset
    Set rs = Me.RecordSetClone
    rs.Find "[id] = " & Me.YourComboBox.Value
    Me.Bookmark = rs.Bookmark
Hopefully, this information will be of use to someone!

Back to Usenet Musings

Problems with this site? Please contact the
with your comments, questions, or suggestions.