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:
Hopefully, this information will be of use to someone!
- 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
- 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
Problems with this site? Please contact the
with your comments, questions, or suggestions.