Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Working with DAO Recordset Properties and Methods

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.

Creating a Recordset Variable

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.

Listing 14.49 Opening a Recordset

Sub OpenTable()
Dim dbInfo As dao.Database
Dim rstClients As dao.Recordset
'Point the Database object at a reference to the
'current database
Set dbInfo = CurrentDb()
'Open a recordset based on the tblClients table
Set rstClients = dbInfo.OpenRecordset("tblClients")
'Print the Updatable property of the recordset
Debug.Print rstClients.Updatable
End Sub

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.

Listing 14.50 Opening a Dynaset-Type Recordset on a Table

Sub OpenDynaset()
Dim dbInfo As dao.Database
Dim rstClients As dao.Recordset
'Point the Database object at a reference to the
'current database
Set dbInfo = CurrentDb()
'Open a dynaset-type recordset based on the tblClients table
Set rstClients = dbInfo.OpenRecordset("tblClients", dbOpenDynaset)
'Print the Updatable property of the recordset
Debug.Print rstClients.Updatable
End Sub

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.

Listing 14.51 Opening a Recordset Based on a Query

Sub OpenQuery()
Dim dbInfo As dao.Database
Dim rstClients As dao.Recordset
'Point the Database object at a reference to the
'current database
Set dbInfo = CurrentDb()
'Open a snapshot-type recordset based on the qryHoursByProject query
Set rstClients = dbInfo.OpenRecordset("qryHoursByProject", dbOpenSnapshot)
'Print the Updatable property of the recordset
Debug.Print rstClients.Updatable
End Sub

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.

Using Arguments to Open a Recordset

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.

Listing 14.52 Using an OpenRecordset Argument

Sub OpenRecordsetArgs()
Dim db As dao.Database
Dim rst As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb
'Open a dynaset-type recordset based on the tblProjects table,
'but designate that the recordset is read-only
Set rst = db.OpenRecordset("tblProjects", dbOpenDynaset, dbReadOnly)
'Print the Updatable property of the recordset
Debug.Print rst.Updatable
End Sub

This code opens a recordset as read-only.

Examining Record-Movement Methods

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.

Table 14.5. Methods for Moving Through the Records in a Recordset

Method	Moves
MoveFirst	To the first record in a recordset
MoveLast	To the last record in a recordset
MovePrevious	To the previous record in a recordset
MoveNext	To the next record in a recordset
Move[0]	Forward or backward a specified number of records

Method

Moves

MoveFirst

To the first record in a recordset

MoveLast

To the last record in a recordset

MovePrevious

To the previous record in a recordset

MoveNext

To the next record in a recordset

Move[0]

Forward or backward a specified number of records

Listing 14.53 shows an example of using the record-movement methods on a dynaset.

Listing 14.53 Using the Move Methods

Sub RecordsetMovements()
Dim db As dao.Database
Dim rst As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb
'Open a dynaset-type recordset based on the tblProjects table
Set rst = db.OpenRecordset("tblProjects", dbOpenDynaset)
'Print the contents of the ProjectID field
Debug.Print rst("ProjectID")
'Move to the next row, printing the ProjectID
rst.MoveNext
Debug.Print rst("ProjectID")
'Move to the last row, printing the ProjectID
rst.MoveLast
Debug.Print rst("ProjectID")
'Move to the previous row, printing the ProjectID
rst.MovePrevious
Debug.Print rst("ProjectID")
'Move to the first row, printing the ProjectID
rst.MoveFirst
Debug.Print rst("ProjectID")
rst.Close
End Sub

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.

Detecting the Limits of a Recordset

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.

Listing 14.54 Using the EOF Property with MoveNext

Sub DetermineLimits()
Dim db As dao.Database
Dim rstClients As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a snapshot-type recordset based on the tblClients table
Set rstClients = db.OpenRecordset("tblClients", dbOpenSnapshot)
'Loop through all of the records in the recordset, printing
'the ClientID
Do Until rstClients.EOF
Debug.Print rstClients("ClientID")
rstClients.MoveNext
Loop
rstClients.Close
End Sub

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.

Counting the Number of Records in a 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.

Listing 14.55 Demonstrating the Limitations of RecordCount

Sub CountRecords()
Dim rstProjects As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a snapshot-type recordset based on the tblClients table
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenSnapshot)
'Print the record count
Debug.Print rstProjects.RecordCount 'Prints 0 Or 1
'Move to the last row
rstProjects.MoveLast
'Print the record count
Debug.Print rstProjects.RecordCount
'Prints an accurate record count
rstProjects.Close
End Sub

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.

Listing 14.56 Checking for an Empty Recordset Using RecordCount

Sub CheckARecordset()
Dim db As dao.Database
Dim rstProjects As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a snapshot-type recordset based on the tblEmpty table
Set rstProjects = db.OpenRecordset("tblEmpty", dbOpenSnapshot)
'Execute the AreThereRecords function to determine if the
'recordset contains any rows
If Not AreThereRecords(rstProjects) Then
MsgBox "Recordset Empty...Unable to Proceed"
End If
End Sub
Function AreThereRecords(rstAny As Recordset) As Boolean
'Return the RecordCount property of the recordset
'received as a parameter
AreThereRecords = rstAny.RecordCount
End Function

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.

Sorting, Filtering, and Finding Records

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.

Sorting a Recordset

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.

Listing 14.57 Sorting an Existing Recordset

Sub SortRecordset()
Dim db As dao.Database
Dim rstTimeCardHours As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb
'Open a dynaset-type recordset based on tblTimeCardHours
Set rstTimeCardHours = db.OpenRecordset("tblTimeCardHours", dbOpenDynaset)
'Loop through the unsorted recordset
Debug.Print "NOT Sorted!!!"
Do Until rstTimeCardHours.EOF
Debug.Print rstTimeCardHours("DateWorked")
rstTimeCardHours.MoveNext
Loop
'Loop through the sorted recordset
Debug.Print "Now Sorted!!!"
rstTimeCardHours.Sort = "[DateWorked]"
Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
Do Until rstTimeCardHours.EOF
Debug.Print rstTimeCardHours("DateWorked")
rstTimeCardHours.MoveNext
Loop
End Sub

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

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.

Listing 14.58 Filtering an Existing Recordset

Sub FilterRecordSet()
Dim db As dao.Database
Dim rstTimeCardHours As Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb
'Open a dynaset-type recordset based on tblTimeCardHours
Set rstTimeCardHours = db.OpenRecordset("tblTimeCardHours", dbOpenDynaset)
'Loop through the unfiltered recordset
Debug.Print "Without Filter"
Do Until rstTimeCardHours.EOF
Debug.Print rstTimeCardHours("DateWorked")
rstTimeCardHours.MoveNext
Loop
'Loop through the filtered recordset
rstTimeCardHours.Filter = "[DateWorked] Between #1/1/95# and #1/5/95#"
Debug.Print "With Filter"
Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
Do Until rstTimeCardHours.EOF
Debug.Print rstTimeCardHours("DateWorked")
rstTimeCardHours.MoveNext
Loop
End Sub

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.

Finding a Specific Record Within a Recordset

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.

Listing 14.59 Using the Seek Method

Sub SeekProject(lngProjectID As Long)
Dim db As dao.Database
Dim rstProjects As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a table-type recordset based on the tblProjects table
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenTable)
'Set the Index property of the recordset and
'use the Seek method to find a project
rstProjects.Index = "PrimaryKey"
rstProjects.Seek "=", lngProjectID
'Determine if the requested row was found
If rstProjects.NoMatch Then
MsgBox lngProjectID & " Not Found"
Else
MsgBox lngProjectID & " Found"
End If
End Sub

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.

Listing 14.60 Using the FindFirst Method

Sub FindProject(lngValue As Long)
Dim db As dao.Database
Dim rstProjects As dao.Recordset
Dim sSQL As String
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a dynaset-type recordset based on tblProjects
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)
'Find the first row meeting the specified criteria
sSQL = "[ProjectID] = " & lngValue
rstProjects.FindFirst sSQL
'Determine if a match was found
If rstProjects.NoMatch Then
MsgBox lngValue & " Not Found"
Else
MsgBox lngValue & " Found"
End If
End Sub

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.

Using the AbsolutePosition Property

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.

Listing 14.61 Specifying Where a Record Was Found

Sub FindPosition(lngValue As Long)
Dim db As dao.Database
Dim rstProjects As dao.Recordset
Dim sSQL As String
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a dynaset-type recordset based on tblProjects
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)
'Find the first row meeting the specified criteria
sSQL = "[ProjectID] = " & lngValue
rstProjects.FindFirst sSQL
'If a match is found, print the position of the row
If rstProjects.NoMatch Then
MsgBox lngValue & " Not Found"
Else
Debug.Print rstProjects.AbsolutePosition
End If
End Sub

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.

Using the Bookmark Property

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.

Listing 14.62 Using a Bookmark

Sub UseBookmark()
Dim db As dao.Database
Dim rstProjects As dao.Recordset
Dim sSQL As String
Dim vntPosition As Variant
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a dynaset-type recordset based on tblProjects
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)
'Store the current position in a variant variable
vntPosition = rstProjects.Bookmark
'Process the recordset
Do Until rstProjects.EOF
Debug.Print rstProjects("ProjectID")
rstProjects.MoveNext
Loop
'Move back to the bookmarked row
rstProjects.Bookmark = vntPosition
Debug.Print rstProjects("ProjectID")
End Sub

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.

Using the RecordsetClone Property

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.

Listing 14.63 Using the RecordsetClone Property

Private Sub cmdFindClient_Click()
'This code is not found in the sample database
Me.RecordsetClone.FindFirst "ClientID = " & Me.txtClientID
If Me.RecordsetClone.NoMatch Then
MsgBox Me.txtClientID & " Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

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.

Running Parameter Queries

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.

Listing 14.64 Using Parameter Queries

Sub RunParameterQuery(datStart As Date, datEnd As Date)
Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim rst As dao.Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb
'Point the QueryDef object at the qryBillAmountByClient query
Set qdf = db.QueryDefs("qryBillAmountByClient")
'Set the parameters of the QueryDef object
qdf.Parameters("Please Enter Start Date") = datStart
qdf.Parameters("Please Enter End Date") = datEnd
'Open a recordset based on the QueryDef object
Set rst = qdf.OpenRecordset
'Loop through the resulting recordset
Do Until rst.EOF
Debug.Print rst("CompanyName"), rst("BillAmount")
rst.MoveNext
Loop
End Sub

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.