- RecordSource
- Recordset
- RecordsetClone
Reading
In the Access 2003 help file read the entries for- RecordsetClone
- RecordSource
- Recordset (DAO)
- Bookmark
- Me object
- FindFirst, FindNext, FindLast, FindPrevious methods
- Balter pages 684 - 700 DAO Recordset properties and methods, the RecordsetClone property and Bookmarks, 721 - 724 using the immediate window
- (for Access 2000 Balter pages 468-482 DAO Recordset properties and methods, the RecordsetClone property, Bookmarks page 501-2 immediate window)
Introduction - DAO and ADO
Background Reading (for interest only)
Balter pages 672 - 684
( Access 2000 Balter pages 430 - 468 Jennings Chapter 27, pages 1032 - 1082 )Commentary
Microsoft currently provide two different ways of accessing data (strictly interface definitions), Data Access Objects (DAO) and ActiveX Data Objects (ADO). DAO was the only model in Visual Basic up to VB5 and in Access up to Access 97. With VB6 and from Access 2000, Microsoft have made both DAO and ADO available.Microsoft have also made it clear that their intention is to move to ADO exclusively in the long term. Certain important products/environments such as ASP scripting using VB Script only support ADO and anyone planning to use Microsoft products long term should learn the use of ADO.
As stated below, a database created in Access 2003 uses DAO by default as does a database created in Access 97 but Access2000 uses ADO. So the default in the databases we have used so far, which were converted from Access 97.
For this reason, and because it is a bit simpler, the rest of this module will use DAO objects and methods to be consistent with the methods on the default objects created by Access.
Very Important - References
Access 2003 is set up so that when you create a database it expects you to use DAO to access data. You can see this as follows.If you set up a new database in Access 2003 and then open the VBA IDE (either from a Form or a new Module) and then choose Tools, References from the Toolbar, you will get a window showing the places VBA will look for definitions of language methods etc. What you will see is
Native Access 2003 references - Data Access Objects (DAO) compliant but ActiveX Data Objects (ADO) also referenced
Native Access 2000 references - ActiveX Data Objects (ADO) compliant
References present in databases converted from Access 97 to Access 2000 format
If you create a database in Access 97 and then convert it to Access 2000 format and then look at the references here you will see
Both the references selected and the order in which they appear are important. The ones selected will be the only ones searched and they will be searched in the order given, so you see an Access 2000 created database will only look for ADO data manipulation, while an Access 97 database converted to Access 2000 will only look for DAO! If you add extra references and these lead to duplicate definitions then the first in the list will be used, so, if you had ADO 2.1 and DAO 3.6 in that order, since both contain definitions of the OpenDatabase method for Database objects, Access 2000 would use the ADO definition even though the DAO one was there. So it is no good just adding the DAO 3.6 to the end of the list!
Note that an Access2003 created database has both the DAO and ADO libraries selected (very dangerous) which means that DAO classes will be accessed first but, if you use a class which is defined for ADO only, that class will be used. Thus, if you think you are programming using ADO then any classes which are also defined for DAO will be created as DAO objects, while classes which are unique to ADO will be created as ADO objects, leading to impenetrable bugs.
Probably it is the most sensible to deselect (untick) the reference to the data access classes you don't intend to use.
The PROJCONT (and other) databases supplied with these notes originate from Access 97 databases and so have the DAO references and will execute the DAO code.
Very Important If you create a database in Access 2000 and you want to write DAO code in it, you must change the references from the native Access 2000 list to the native Access 97 list as shown above - otherwise you will get a series of inexplicable and meaningless error messages as Access tries to execute the commands in their ADO form!
If you create a database in Access2003 and intend to write data access VBA, you should unselect the library for the data access objects you don't intend to use.
File format
Microsoft has implemented (at least) 3 different file formats with different compatibilities| Access 97 can develop | Access2000 can develop | Access2003 can develop | |
| Access97 format | Yes | Can convert (both ways) but not develop | Can convert (both ways) but not develop |
| Access2000 format | No | Yes | Yes (can also create) |
| Access2002-3 format | No | No | Yes |
In this module we'll try to stick to Access2000 format files compatible with both Access200 and Access2003.
Discussion
Underlying any data bound form there is a table or query which supplies the data displayed on the form. In VBA or Visual Basic terminology, this virtual table is called a Recordset. Recordsets can be free standing or, as in the case of Access forms (and Reports) properties of other objects. The contents of the Recordset are determined by the RecordSource property and you can change the Recordset by changing the Recordsource (or, as we will see later, by applying a Filter expression). You can work with a read-only copy of a Form's Recordset by referencing it's RecordsetClone property and you can perform actions (such as repositioning) on the RecordsetClone without changing the current position on the underlying data which determines what is being displayed.If you want to match the position of the displayed data with that in the RecordsetClone, you can do this with the Bookmark properties of the two Recordsets by assigning the value of the Bookmark of the clone to Me.Bookmark (implicitly the Bookmark property of the recordset underlying the form).
The following is an extract from the Help entry for RecordsetClone.
You can use the RecordsetClone property to refer to a form's Recordset object specified by the form's RecordSource property.
Setting
The RecordsetClone property setting is a copy of the underlying query or table specified by the form's RecordSource property. If a form is based on a query, for example, referring to the RecordsetClone property is the equivalent of cloning a Recordset object by using the same query. If you then apply a filter to the form, the Recordset object reflects the filtering.
This property is available only by using Visual Basic and is read-only in all views.
Practical Example
In this practical example you will see how to move through the RecordsetClone and how to synchronise the underlying data with the operation being shown by using the Debug window.- Create a form based on the CUSTOMER table using the Form Wizard and call it frmCUSTOMERrecordsetclone
- Put two buttons on the form and type into them the code below for the click events (here for Command10_Click and Command11_Click)
- View the form and type
G to show the immediate window - NB in Access 2003 the VBA IDE is a separate window and, so, the Immediate Window opens within this, not within the Access window (unlike Access 97), so you will have to swap between them to see what is going on. - Click each of the buttons. You will see with the first button the table data is displayed in the debug window but the data displayed is not changed while the second button displays the last row in the table after looping through the table
Option Compare Database
Option ExplicitPrivate Sub Command10_Click()
With Me.RecordsetClone
.MoveFirst
Me.Bookmark = .Bookmark
Do While Not .EOF
Debug.Print !Custno, !Customer
.MoveNext
Loop
End WithEnd Sub
Private Sub Command11_Click()
With Me.RecordsetClone
.MoveFirst
Me.Bookmark = .BookmarkDo While Not .EOF
Debug.Print !Custno, !Customer
.MoveNext
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
Loop
End WithEnd Sub
Commentary on Code
Command10_Click()- The With block sets up all the references (preceded with a .) inside the block refer to Me.RecordsetClone
- .MoveFirst positions in the RecordsetClone at the first row
- Me.Bookmark = .Bookmark synchronises the recordset underlying the data with the position in the RecordsetClone (ie at the first row)
- The Do While loop executes as long as the program is not at the end of the RecordsetClone recordset (after the last row)
- The Debug.Print prints each Customer Number and Customer name to the debug window
- .MoveNext moves to the next row in RecordsetClone
- Does exactly the same but synchronises the underlying data with the RecordsetClone each time through the loop, except when the RecordsetClone has gone past the last row, thus leaving the underlying data displaying the last row at the end of the loop
No comments:
Post a Comment