Like other objects, Recordset objects have properties and methods. The properties are the attributes of the Recordset objects, and the methods are the actions you can take on the Recordset objects. Some properties are read-only at runtime; others can be read from and written to at runtime.
When working with a recordset, you must first create a Recordset variable. You use the OpenRecordset method to create a Recordset object variable. You must first declare a generic Recordset variable and then point a specific recordset at the variable using a Set statement, as shown in the example in Listing 14.49.
This code creates a Database object variable and a Recordset object variable. It then uses the CurrentDB function to point the Database object variable to the current database. Next, it uses the OpenRecordset method to assign the recordset based on tblClients to the object variable rstClients.
The type of recordset that is created is determined by the default type for the object or by a second parameter of the OpenRecordset method. If the OpenRecordset method is executed on a table and no second parameter is specified, the recordset is opened as the table type. If the OpenRecordset method is performed on a query and no second parameter is specified, the recordset is opened as the dynaset type. You can override this default behavior by passing a second parameter to the OpenRecordset method, as Listing 14.50 shows.
This code opens the recordset as a dynaset. dbOpenTable, dbOpenDynaset, and dbOpenSnapshot are all intrinsic constants that can be used to open a Recordset object. A query can be opened only as a dynaset or snapshot Recordset object. Listing 14.51 shows the code to open a recordset based on a query.
NOTE
As was the case with Access 95, the proper method to create a Recordset object in Access 97 and above differs from that of earlier versions of Access. In earlier versions, it was appropriate to dimension a dynaset, snapshot, or table type of object variable and then use the CreateDynaset, CreateSnapshot, and OpenTable methods of the Database object to create the appropriate type of recordset. This method for creating recordsets is included in Access 97 and above for backward compatibility only. It should be avoided and replaced with the code included in this section.
Microsoft provides several arguments that control the way in which a recordset is opened. The arguments and their uses follow:
dbAppendOnly When this option is used, records can be added to the recordset only. Existing data cannot be displayed or modified. This option is useful when you want to ensure that existing data is not affected by the processing. This option applies to dynasets only.
dbConsistent This argument applies to dynasets. It allows consistent updates only. This means that in a one-to-many join, you can update only those fields that are not duplicated in other records in the dynaset. This is the default argument for dynasets.
dbDenyRead This argument prevents other users from even reading the data contained within the recordset as long as the recordset remains open. You can use this option only on table recordsets.
dbDenyWrite When creating a dynaset or snapshot, this option prevents all other users from modifying the records contained in the recordset until the recordset is closed. Other users still are able to view the data contained within the recordset. When this option is applied to a table type of recordset, other users are prevented from opening the underlying table.
dbForwardOnly This argument creates a forward-scrolling snapshot. This type of recordset is fast but limited, because you can use only the Move and MoveNext methods to move directly through the snapshot.
dbInconsistent This argument allows for inconsistent updates. This means that, in a one-to-many join, you can update all columns in the recordset.
dbReadOnly This option prevents your recordset from modifying data. If you don't want the data within the recordset to be updateable, but you expect a large number of records to be returned and you want to take advantage of the record paging offered by dynasets, you might want to open the recordset as a dynaset.
dbSeeChanges This option ensures that a user receives an error if the code issues an Edit method and another user modifies the data before an Update method is used. This option is useful in a high-traffic environment when it is likely that two users will modify the same record at the same time. This option applies to dynaset and table recordsets only.
dbSQLPassThrough When the source of the recordset is a SQL statement, this argument passes the SQL statement to an ODBC database for processing. This option does not completely eliminate Jet; it simply prevents Jet from making any changes to the SQL statement before passing it to the ODBC Drive Manager. You can use the dbSQLPassThrough argument only with snapshots and read-only dynasets.
The arguments described can be used in combination to accomplish the desired objectives. Listing 14.52 shows the use of an OpenRecordSet argument.
This code opens a recordset as read-only.
When you have a Recordset object variable set, you probably want to manipulate the data in the recordset. Table 14.5 shows several methods you can use to traverse through the records in a recordset.
Method | Moves |
|---|---|
MoveFirst | |
MoveLast | |
MovePrevious | |
MoveNext | |
Move[0] |
Listing 14.53 shows an example of using the record-movement methods on a dynaset.
This code opens a dynaset. The record pointer is automatically placed on the first record of the dynaset when the recordset is opened. The routine prints the contents of the ProjectID field and then moves to the next record, printing its ProjectID. It then moves to the previous, first, and last records of the dynaset, printing their respective ProjectIDs. The Close method is applied to the Recordset object. It is a good idea to always close an open recordset before exiting a routine. After changes are made to the recordset, the Close method properly closes the recordset, ensuring that all changes are written to disk.
All the information discussed in the section about determining the limits of an ADO recordset apply when dealing with a DAO recordset. Listing 14.54 shows a DAO code sample that uses the EOF property with the MoveNext method.
This code traverses through a snapshot recordset, printing the value of the ClientID field for each record until it reaches the position after the last record in the recordset. It then exits the loop and closes the recordset.
The RecordCount property of a recordset returns the number of records in a recordset that have been accessed. The problem with this is evident if you open a recordset and view the RecordCount property. You will discover that the count is equal to 0 if no records exist in the recordset, or equal to 1 if there are records in the recordset. The record count is accurate only if you visit all the records in the recordset, which you can do by using the MoveLast method, as Listing 14.55 shows.
The MoveLast method has its problems, though. It is slow and inefficient, especially in a client/server environment. Furthermore, in a multiuser environment, the RecordCount property becomes inaccurate as other people add and remove records from the table. This means that, if determining the record count is not absolutely necessary, you should avoid it.
The RecordCount property has one good use: You can use it to see whether there are any records in a recordset. If you are performing an operation that might return an empty recordset, you can easily use the RecordCount property to determine whether records were returned, as Listing 14.56 shows.
The CheckARecordset procedure opens a recordset based on the tblEmpty table. It then calls the AreThereRecords function to determine whether any records are found in the recordset. If the AreThereRecords function returns False, an error message is displayed to the user.
Sometimes you might need to sort or filter an existing recordset. You also might want to locate each record in the recordset that meets some specified criteria. The following techniques enable you to sort, filter, and find records within a Recordset object.
You can't actually change the sort order of an existing dynaset or snapshot. Instead, you create a second recordset based on the first recordset. The second recordset is sorted in the desired order. Listing 14.57 shows how this process works.
In this case, you are sorting a dynaset based on the tblTimeCardHours table. The first time you loop through the recordset and print each date worked, the dates are in the default order (usually the primary key order). After using the Sort method to sort the recordset, the records appear in order by the date worked.
Filtering a recordset is a useful technique when you want to select a subset of the records in your recordset. This is especially useful for allowing users to drill-down on a set of records to find the subset they need.
The process of filtering an existing recordset is similar to sorting one. Listing 14.58 is a variation of the example in Listing 14.57. Instead of sorting an existing recordset, it filters an existing recordset.
The first time the code loops through the recordset, no filter is set. Then the code sets the filter, and the code loops through the recordset again. The second time, only the records meeting the filter criteria are displayed.
The Seek method enables you to find records in a table recordset. It is usually the quickest method of locating data because it uses the current index to locate the requested data. Listing 14.59 shows how the Seek method works.
This code uses the primary key index to locate the first project with the project number that was passed to the function. It then displays a message box to indicate whether the value was found.
You cannot use the Seek method to locate data in a dynaset or snapshot. Furthermore, you cannot use Seek to search for records in a linked table, regardless of whether the linked table is an Access table or a client/server table. In this case, you must use the FindFirst, FindLast, FindNext, and FindPrevious methods. The FindFirst method finds the first occurrence of data that meets the criteria, and FindLast finds the last occurrence of such data. The FindNext and FindPrevious methods enable you to find additional occurrences of the data.
The code in Listing 14.60 uses the FindFirst method to find the first occurrence of the parameter that was passed in. Again, it displays an appropriate message box.
TIP
You can use another trick to search a linked table. You can open the database that contains the linked table and seek directly on the table data. This works only if the linked table is in another Access database.
The AbsolutePosition property returns the position of the current record. It is a zero-based value. You can use it to specify where in a recordset a specific record was found, as shown in Listing 14.61.
This code finds the first record with a ProjectID equal to the long integer received as a parameter. If the ProjectID is found, the value in the AbsolutePosition property of the record is printed.
CAUTION
Do not assume that the AbsolutePosition property of a particular record will stay the same. The AbsolutePosition property of a record changes as records are added or deleted or their order is changed as the records are modified.
A
bookmark is a system-generated byte array that uniquely identifies each record in a recordset. The Bookmark property of a recordset changes as you move to each record in the recordset. It often is used if you need to store the current position in the recordset so that you can perform some operation and then return to the position after the operation is completed. Three steps are involved in this process:
Storing the current bookmark of the recordset to a Variant variable.
Performing the desired operation.
Setting the Bookmark property of the recordset to the value contained within the Variant variable.
Listing 14.62 shows an example of using a bookmark.
This code begins by opening a recordset and storing the bookmark of the first record into a Variant variable. It then loops through each record in the recordset, printing the value in the ProjectID. After the loop completes, the Bookmark property of the recordset is set equal to the Variant variable, setting the current position of the recordset back to where it was before the loop began processing.
You use the RecordsetClone property of a form to refer to the recordset underlying the form. You will often use this property when you want to perform an operation and then synchronize the form with its underlying recordset. Listing 14.63 shows an example of the RecordsetClone property.
This routine performs the FindFirst method on the RecordsetClone property of the current form. If the record is found, the code sets the Bookmark property of the form equal to the bookmark of the recordset. This matches the form's position to the underlying recordset's position.
Access parameter queries are very powerful. They enable the user to specify criteria at runtime. This capability often is helpful if your user wants to fill out a form at runtime and have the values on that form fed to the query. This also can be a useful way to protect your code from changes in the database schema. Creating a parameterized query is like writing a subroutine, in which the details of implementing that routine are hidden from the caller. This programming technique is called
encapsulation . Listing 14.64 shows an example of using parameter queries.
This subroutine receives two date variables as parameters. It just as easily could receive form controls as parameters. It opens a query definition called qryBillAmountByClient. It then sets the values of the parameters called Please Enter Start Date and Please Enter End Date to the date variables passed into the subroutine as parameters. The query then is executed by issuing the OpenRecordset method on the Recordset object.