DAO Object Model: The Definitive Reference By Helen Feddema 1st Edition January 2000 1-56592-435-5, Order Number: 4355 400 pages, $34.95, Includes CD-ROM

Chapter 8:
Recordsets Collection and Recordset Object

The Recordset object is the primary object used to manipulate data in Access databases (and other databases as well, via ODBC connections). Although there is a Recordsets collection (the collection of all open Recordset objects in a database), it is not much use, except to list the open recordsets and their SQL statements, as in the following code sample:

Private Sub cmdListRecordsets_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim intCount As Integer
   Dim strTable As String
   
   strTable = "Orders"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   intCount = dbs.Recordsets.Count
   Debug.Print intCount & _
         " recordsets in current database (before opening a recordset)"
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   intCount = dbs.Recordsets.Count
   Debug.Print intCount & _
         " recordsets in current database (after opening a recordset)"
   
   For Each rst In dbs.Recordsets
      Debug.Print "Open recordset: " & rst.Name
   Next rst
   
End Sub

A new Recordset object is automatically added to the Recordsets collection when you open the recordset, and it is automatically removed when you close it. Note that when you first count the recordsets in the preceding code, the count is 0; after setting a recordset variable, it is 1. The position of the Recordsets collection in the DAO object model is shown in Figure 8-1.

Figure 8-1. The Recordsets collection in the DAO object model

You can create as many recordset variables as you want, and different recordsets can access the same tables, queries, or fields without causing a problem. You can even open two recordsets from the same data source, and this is not a problem, so long as you refer to the recordsets by the variables used to assign them, not by their names.

TIP:  

See the Name property section later in this chapter for a discussion of why it is unwise to use the Name property to reference a recordset.

There are five types of recordsets: Table-type, Dynaset, Snapshot, Forward-only, and Dynamic. (See the Type property section for a discussion of recordset types.) Each Recordset object contains a collection of Fields that represents the fields in the underlying table(s). You can list the field names and values, but you will just get the values in the current record, unless you first go to a particular record. For example, the following code moves to the last record in a recordset and lists the field names and values for that record:

Private Sub cmdListFields_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   Dim fld As Field
   
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   With rst
      .MoveLast
      For Each fld In .Fields
         Debug.Print fld.Name & " value: " & fld.Value
      Next fld
      .Close
   End With
   
End Sub

Although you can reference a Recordset object in the Recordsets collection by any of the following syntax variants, it is advisable to use its variable instead to avoid possible ambiguity.

Recordsets(0)
Recordsets("name")
Recordsets![name]

The Recordsets collection has two properties and one method, which are shown in Table 8-1.

Table 8-1: Members of the Recordsets Collection

Type	Name	Description
Property	Count	Indicates the number of Recordset objects in the Recordsets collection
Property	Item	Retrieves a Recordset object either by its index in the collection or by its name
Method	Refresh	A nonfunctional method

The Recordset object has 32 properties (shown in Table 8-2) and 24 methods (listed in Table 8-3). It also has two collections: the Fields collection, a collection of the individual fields in the recordset; and the Properties collection, a collection of the individual properties supported by the Recordset object.

Table 8-2: Recordset Object Properties

Property	Description
AbsolutePosition	The relative position of the current record
BatchCollisionCount	Number of records that did not complete the last batch update
BatchCollisions	Array of bookmarks representing rows that had a collision during the last batch update
BatchSize	For batch updates, the number of statements to send back to the server in each batch
BOF	Flag indicating whether the record pointer is at the beginning of the file
Bookmark	Value that uniquely identifies a particular record in the recordset
Bookmarkable	Indicates whether a recordset supports bookmarks
CacheSize	Determines the number of records from an ODBC data source that will be cached locally
CacheStart	The bookmark of the first record to be cached locally
Connection	The Connection object that owns the recordset
DateCreated	Date and time the recordset was created
EditMode	Indicates the recordset's editing state
EOF	Flag indicating whether the record pointer is at the end of the file
Filter	Expression that filters records from the recordset
Index	Name of the current index
LastModified	Bookmark indicating the most recently modified record
LastUpdated	Date and time the recordset was last updated
LockEdits	The type of locking in effect when editing
Name	The name of the Recordset object
NoMatch	Flag indicating whether a search was successful
PercentPosition	Relative position of the current record in the recordset
RecordCount	Number of records in the recordset
RecordStatus	The update status of the current record in the next batch update
Restartable	Indicates whether a recordset supports the Requery method
Sort	Expression defining the sort order of records
StillExecuting	Indicates whether an asynchronous operation has finished executing
Transactions	Indicates whether the recordset supports transactions
Type	The recordset (or cursor) type
Updatable	Indicates whether the recordset can be updated
UpdateOptions	Indicates how the SQL WHERE clause is constructed for each record during a batch update and whether the update should use an UPDATE statement or a DELETE followed by an INSERT
ValidationRule	Defines a validation rule used to validate data as it is changed
ValidationText	Text displayed when a record fails to meet the validation criteria

Table 8-3: Recordset Object Methods

Method	Description
AddNew	Adds a new record to an updatable recordset
Cancel	Cancels execution of a pending asynchronous method call
CancelUpdate	Cancels any pending updates
Clone	Creates a duplicate Recordset object
Close	Closes the recordset
CopyQueryDef	Returns a copy of the QueryDef object originally used to create the recordset
Delete	Deletes the current record
Edit	Prepares a record for editing
FillCache	Fills all or part of a recordset's local cache
FindFirst	Finds the first record that meets designated criteria
FindLast	Finds the last record that meets designated criteria
FindNext	Finds the next record that meets designated criteria
FindPrevious	Finds the previous record that meets designated criteria
GetRows	Retrieves rows into a two-dimensional array
Move	Moves the record pointer either forward or backward
MoveFirst	Moves to the first record of the recordset
MoveLast	Moves to the last record of the recordset
MoveNext	Moves to the next record of the recordset
MovePrevious	Moves to the previous record of the recordset
NextRecordset	Retrieves the next set of records returned by a multipart SELECT query
OpenRecordset	Creates a new recordset
Requery	Reissues the query that created the recordset
Seek	Locates a record that meets the criteria based on the current index
Update	Saves changes to a record

Access to the Recordset Object

Creatable

No

Returned by

The OpenRecordset method of the Connection object
The Recordsets property of the Connection object
The OpenRecordset method of the Database object
The Recordsets property of the Database object
The Clone method of the Recordset object
The OpenRecordset method of the Recordset object
The OpenRecordset method of the TableDef object

Recordsets Collection Properties

Count

Data Type

Integer

Description

Indicates the number of recordsets in the Recordsets collection. See the code sample in the introduction to this chapter for an example of its usage. As far as I can see, there is little (if any) practical use for this property.

Item
Recordsets.Item(Index)

Argument	Data Type	Description
Index	Integer	The ordinal position of the Recordset object in the Recordsets collection, or a string containing the name of the Recordset object to be retrieved from the collection

Data Type

Recordset object

Description

Retrieves a particular Recordset object from the Recordsets collection. A Recordset object can be retrieved either based on its ordinal position in the collection or based on its name. (But see the Name section later in this chapter for the reasons why it is unwise to reference a recordset based on its name.) Mostly, recordsets are manipulated by means of the variable used to set them, since this is the best way to assure that you are working with the correct recordset.

Recordsets Collection Methods

Refresh

---

Recordsets.Refresh

Although the documentation lists Refresh as a method of the Recordsets object in Help and the Object Browser, actually this method is inapplicable to the Recordset object, since Refresh applies only to persistent objects. (Since recordsets exist only in memory, the Recordsets collection is not a collection of persistent objects.) Calling the method, however, does not generate an error; it simply has no effect.

Recordset Object Properties

Table 8-4 summarizes which properties apply to each type of Recordset object and whether the property setting is read/write (RW) or read-only (RO) for Jet ( J) and ODBCDirect (O) databases, or for both Jet and ODBCDirect workspaces ( JO). A blank cell indicates that the property does not apply to either type of workspace. In cases where the property is always read-only with a value of False, that is indicated by an F in the cell.

Table 8-4: Recordset Property Summary

Property	Table	Dynaset	Snapshot	Forward-Only	Dynamic
AbsolutePosition		RW	RW		RW
		JO	JO		O
BatchCollisionCount		RO	RO	RO	RO
		O	O	O	O
BatchCollisions		RO	RO	RO	RO
		O	O	O	O
BatchSize		RW	RW	RW	RW
		O	O	O	O
BOF	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
Bookmark	RW	RW	RW		RW
	J	JO	JO		O
Bookmarkable	RO	RO	RO		RO
	J	JO	JO		O
CacheSize		RW/RO	RO		RO
		J/O	O		O
CacheStart		RW
		J
Connection		RW	RW	RW	RW
		O	O	O	O
DateCreated	RO
	J
EditMode	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
EOF	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
Filter		RW	RW	RW
		J	J	J
Index	RW
	J
LastModified	RO	RO	RO		RO
	J	JO	O[1]		O
LastUpdated	RO
	J
LockEdits	RW	RW/RO	RW/RO	RW	RO
	J	J/O	J/O	J	O
Name	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
NoMatch	RO	RO	RO
	J	J	J
PercentPosition	RW	RW	RW		RW
	J	JO	JO		O
RecordCount	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
RecordStatus		RO	RO	RO	RO
		O	O	O	O
Restartable	F	RO	RO	RO	RO
	J	JO	JO	JO	O
Sort		RW	RW
		J	J
StillExecuting		RO	RO	RO	RO
		O	O	O	O
Transactions	RO	RO	RO (F)	RO (F)
	J	J	J	J
Type	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
Updatable	RO	RO	F/RO	F/RO	RO
	J	JO	J/Oa	J/Oa	O
UpdateOptions		RW	RW	RW	RW
		O	O	O	O
ValidationRule	RO	RO	RO	RO
	J	J	J	J
ValidationText	RO	RO	RO	RO
	J	J	J	J

AbsolutePosition

---

Data Type

Long

Description

This misleadingly named property sets or returns the relative record number of a recordset's current record. It is a zero-based number corresponding to the ordinal position of the current record in the recordset, ranging from zero to one less than the record count. If there is no current record (for example, for an empty recordset), AbsolutePosition returns -1.

TIP:  

Despite the "Absolute" in the property name, this property is not stable and certainly is not a record number. It changes every time records are added to or deleted from a recordset. Use the Bookmark property to set a variable you can use to return to a particular record after moving the record pointer by a search or requerying.

VBA Code

This code displays the ordinal number of the record in a recordset while iterating through it:

Private Sub cmdAbsolutePosition_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim rst As Recordset
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset("Employees", dbOpenSnapshot)
   With rst
      .MoveFirst
      Do While Not .EOF
         Debug.Print !LastName & " record--No. " & .AbsolutePosition + 1
         .MoveNext
      Loop
      .Close
   End With
   dbs.Close
 
End Sub
 

BatchCollisionCount

Data Type

Long

Description

For ODBCDirect workspaces only, this property returns the number of records that did not complete in the last batch update. It corresponds to the number of Bookmarks in the BatchCollisions property.

BatchCollisions

Data Type

Variant Array

Description

For ODBCDirect workspaces only, this property returns a variant containing an array of bookmarks, representing rows that had a collision during the last batch Update call. The number of elements in the array can be determined by retrieving the value of the BatchCollisionCount property.

BatchSize

---

Data Type

Long

Description

For ODBCDirect workspaces only, this property sets or returns the number of statements sent back to the server in each batch. The default value is 15. Setting BatchSize to 1 causes each statement to be sent separately; you might do this when working with those database servers that don't support batch updates.

BOF

---

Data Type

Boolean

Description

The BOF property (the name is derived from "Beginning of File") indicates that the current record position is before the first record in a recordset. It is useful for determining whether you have gone beyond the beginning of the records in a recordset when moving backward. In a recordset with no objects, this property is True. However, if you delete the last remaining record in a recordset, BOF may remain False until you attempt to reposition the record pointer. See Table 8-5 for a summary of what happens when you use the Move methods with different combinations of the BOF and EOF properties.

Table 8-5: The Move Methods with BOF and EOF

BOF/EOF	MoveFirst, MoveLast	MovePrevious, Move < 0	Move 0	MoveNext, Move > 0
BOF=True, EOF=False	Allowed	Error	Error	Allowed
BOF=False, EOF=True	Allowed	Allowed	Error	Error
Both True	Error	Error	Error	Error
Both False	Allowed	Allowed	Allowed	Allowed

Table 8-6 shows what happens to the BOF and EOF properties after using Move methods that don't find a record.

Table 8-6: When Move Methods Don't Find a Record

	BOF	EOF
MoveFirst, MoveLast	True	True
Move 0	No change	No change
MovePrevious, Move < 0	True	No change
MoveNext, Move > 0	No change	True

VBA Code

This code uses the BOF marker to prevent going beyond the first record when iterating backward through a recordset:

Private Sub cmdBOF_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   Dim fld As Field
   
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   With rst
      .MoveLast
      Do While Not .BOF
         Debug.Print !CategoryName
         .MovePrevious
      Loop
      .Close
   End With
   
End Sub

Bookmark

---

Data Type

Variant array of Byte data

Description

Uniquely identifies the current record in a recordset. By retrieving the value of a record's Bookmark property, you can later return the record pointer to that record. It is the closest thing Access has to a record number. You can use bookmarks on recordsets whose Bookmarkable property is True, which is always the case for recordsets based entirely on Jet tables. For recordsets based on other databases, Bookmarkable may not be True, in which case you can't use bookmarks.

VBA Code

See the code sample in the Requery section for an example of using the Bookmark property to return to the same record after requerying; the following code sample shows another way the Bookmark property can be useful in returning to the previous record after a failed search:

Private Sub cmdBookmark_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   Dim varBookmark As Variant
   
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   With rst
      If .Bookmarkable = False Then
         MsgBox "This recordset is not bookmarkable -- exiting!"
         Exit Sub
      Else
         MsgBox "This recordset is bookmarkable -- continuing!"
      End If
      varBookmark = .Bookmark
      .Index = "PrimaryKey"
      .Seek "=", 5
      If .NoMatch Then .Bookmark = varBookmark
      Debug.Print !CategoryName
      .Close
   End With
 
End Sub

Bookmarkable

Data Type

Boolean

Description

The Bookmarkable property indicates whether you can use bookmarks in a recordset. If you are not sure that the tables underlying a recordset are all Jet tables, you can check this property before attempting to use bookmarks. See the Bookmark section for an example of its usage.

CacheSize

---

Data Type

Long

Description

Sets or returns the number of records retrieved from an ODBC data source that will be cached locally. The value must be between 5 and 1200, but it can't be greater than available memory permits. Setting CacheSize to 0 turns off caching. Using a cache can improve performance, since retrieving data from a local cache is faster than retrieving it from the server.

CacheStart

---

Data Type

String

Description

The CacheStart property (used in conjunction with CacheSize and FillCache) sets or returns a value that specifies the bookmark of the first record in a dynaset-type recordset containing the data to be locally cached from an ODBC data source in a Jet workspace.

Connection

---

Data Type

Connection Object

Description

Sets or returns the Connection object that owns the recordset, for ODBCDirect workspaces only.

DateCreated

Data Type

Date/Time

Description

Returns the date and time the recordset was created. Note that this is usually different than the date the underlying table was created.

VBA Code

Private Sub cmdDateCreated_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   Debug.Print rst.Name & " recordset created on " & rst.DateCreated
   
End Sub

EditMode

Data Type

Long

Description

Indicates the state of editing, as listed in Table 8-7.

Table 8-7: The EditMode Property Settings

Named Constant	Value	Description
dbEditNone	0	No editing operation is in progress.
dbEditInProgress	1	The Edit method has been invoked, and the current record is in the copy buffer.
dbEditAdd	2	The AddNew method has been invoked, and the current record in the copy buffer is a new record that hasn't been saved in the database.

The value of the EditMode property can be useful in determining whether you should use the Update or CancelUpdate method when editing is interrupted. The following code sample shows the EditMode value for various stages of editing records.

VBA Code

Private Sub cmdEditMode_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   
   With rst
      .Move 3
      Debug.Print "EditMode before editing: " & .EditMode
      .Edit
      Debug.Print "EditMode after Edit : " & .EditMode
      !Description = "New description of this category"
      .Update
      Debug.Print "EditMode after updating: " & .EditMode
      .AddNew
      Debug.Print "EditMode after AddNew: " & .EditMode
      .CancelUpdate
      Debug.Print "EditMode after canceling editing: " & .EditMode
      .Close
   End With
 
End Sub 

EOF

Data Type

Boolean

Description

The EOF property (derived from "End of File") indicates that the current record position is after the last record in a recordset. It is useful for determining whether you have gone beyond the end of the records in a recordset when moving forward. In a recordset with no objects, this property is True. However, if you delete the last remaining record in a recordset, EOF may remain False until you attempt to reposition the record. See Table 8-5 in the BOF section for a summary of what happens when you use the Move methods with different combinations of the BOF and EOF properties, and Table 8-6 for a listing of what happens to the BOF and EOF properties after using Move methods that don't find a record.

VBA Code

This code uses the EOF marker to prevent going beyond the last record when iterating through a recordset:

Private Sub cmdEOF_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   Dim fld As Field
   
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   With rst
      Do While Not .EOF
         Debug.Print !CategoryName
         .MoveNext
      Loop
      .Close
   End With
   
End Sub

Filter

---

Data Type

String

Description

Sets or returns a value that filters the records in a recordset (for Jet workspaces only). Basically, it is the WHERE clause of a SQL statement without the word WHERE. You can use Filter with dynaset-, snapshot-, or forward-only-type recordsets.

TIP:  

After using the Filter property with a recordset, you don't see the results of filtering immediately--you must open another recordset from the filtered recordset to see the results.

VBA Code

This code sample illustrates using the Filter property to filter records by country, opening a second recordset of UK employees based on the original, unfiltered recordset:

Private Sub cmdFilter_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim rstEmployees As Recordset
   Dim rstUKEmployees As Recordset
   Dim strSearch As String
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenDynaset)
   
   'Create a filtered recordset based on the first recordset
   rstEmployees.Filter = "Country = 'UK'"
   Set rstUKEmployees = rstEmployees.OpenRecordset()
 
   With rstUKEmployees
      Debug.Print vbCrLf & "Filtered recordset:"
      Do While Not .EOF
         Debug.Print "Name: " & !LastName & ", country: " & !Country
         .MoveNext
      Loop
   End With
 
End Sub

TIP:  

Using the Filter property on a recordset is generally less efficient than just applying a filter to a recordset and opening it in one step, using a SQL statement with a WHERE clause.

Index

---

Data Type

String

Description

Sets or returns the name of the index to use for a table-type recordset in a Jet workspace. It must be the name of an existing index in the Indexes collection of the TableDef object that is the data source of the Recordset object. The Index property is used with the Seek method for locating records in an indexed recordset. See the Seek section later in this chapter for an example of usage.

LastModified

Data Type

Variant array of Byte data

Description

Returns a bookmark indicating which record in a recordset was most recently added or modified. It applies to table-type or dynaset-type recordsets only. The primary use of this property is to return to the record that was most recently modified in code by setting the Bookmark property equal to LastModified.

TIP:

The value of LastModified only reflects changes made to the recordset itself; if a record was changed in the interface or directly in the table, this change is not reflected in the LastModified property.

VBA Code

This code loops through tblCustomers (a copy of the Northwind Customers table), modifying records that meet a criterion, and then returns to the last modified record. I'm using the RecordCount property (-1) to avoid being at the EOF marker after looping; otherwise, there would be an error on the first Debug.Print line:

Private Sub cmdLastModified_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strTable As String
   Dim intCount As Integer
   Dim i As Integer
   
   strTable = "tblCustomers"
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   intCount = rst.RecordCount - 1
   
   With rst
      For i = 1 To intCount
         If !Country = "UK" Then
            .Edit
            !Country = "United Kingdom"
            .Update
            Debug.Print "Changed " & !CompanyName & " record"
         End If
         .MoveNext
      Next i
      
      Debug.Print "After looping, at " & !CompanyName & " record"
      'Go to most recently modified record
      .Bookmark = .LastModified
      Debug.Print "Last record modified: " & !CompanyName & " record"
      .Close
   End With
 
End Sub

LastUpdated

Data Type

Date/Time

Description

Returns the date and time the recordset was last updated--in other words, when the data in the base table(s) was last changed. The changes can be made either in the interface or in code (unlike the LastModified property).

VBA Code

Private Sub cmdLastUpdated_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   Debug.Print "Date Created: " & rst.DateCreated
   Debug.Print "Last Updated: " & rst.LastUpdated
 
End Sub

LockEdits

---

Data Type

Boolean

Description

For updatable recordsets the LockEdits property sets or returns a value indicating the type of locking in effect while editing, as shown in Table 8-8. Pessimistic locking (True) means that the page containing the record being edited is unavailable to other users until you are through editing and use the Update method to save the record. Optimistic locking (False) means that other users can access the same record you are working on, except just briefly while you are actually updating the record. Optimistic locking is more risky (two users can simultaneously change a record), but pessimistic locking may cause delays while records are unnecessarily locked.

Table 8-8: The LockEdits Values/Settings

Named Constant	Value	Description
True	-1	(Default) Pessimistic locking is in effect. The 2K page containing the record you're editing is locked as soon as you call the Edit method.
False	0	Optimistic locking is in effect for editing. The 2K page containing the record is not locked until the Update method is executed.

TIP:  

The LockEdits value can be preset by setting the lockedit argument of the OpenRecordset method; setting the lockedit argument to dbPessimistic sets the LockEdits property to True, and setting it to any other value sets LockEdits to False. When working with ODBC data sources, LockEdits is always set to False, allowing only optimistic locking.

Name

Data Type

String

Description

For recordsets, the Name property is either the name of the underlying table or query, or, if the recordset is based on a SQL statement, the first 256 characters of the SQL statement. This makes the Name property unreliable as a means of referencing a particular recordset. To reference a recordset, just use the variable it was set with.

VBA Code

The code lists the names of several recordsets created based on a table, a query, and a SQL statement:

Private Sub cmdName_Click()
 
   Dim dbs As Database
   Dim rstTable As Recordset
   Dim rstQuery As Recordset
   Dim rstSQL As Recordset
   Dim rst As Recordset
   Dim strDBName As String
   Dim intCount As Integer
   Dim strTable As String
   Dim strQuery As String
   Dim strSQL As String
 
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   strTable = "Orders"
   strQuery = "Ten Most Expensive Products"
   strSQL = "SELECT * FROM Customers"
   Set rstTable = dbs.OpenRecordset(strTable, dbOpenTable)
   Set rstSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
   Set rstQuery = dbs.OpenRecordset(strQuery, dbOpenSnapshot)
   
   For Each rst In dbs.Recordsets
      Debug.Print rst.Name
   Next rst
 
End Sub

NoMatch

Data Type

Boolean

Description

Indicates whether a search was successful. It applies to searches done with the Seek method or one of the Find methods.

If a search is unsuccessful (NoMatch = True), the current record will no longer be valid. To avoid problems, save the record's bookmark to a variable so you can return to it after an unsuccessful search, as in the code sample in the Seek section.

PercentPosition

---

Data Type

Single

Description

Sets or returns a value between 0 and 100, representing the position of the current record in a recordset. For dynaset- or snapshot-type recordsets, move to the last record before using this method to ensure an accurate record count. The following code prints the percent position to the Debug window after each successful find of a record meeting a search criterion.

TIP:  

You can use Percent Position with a scroll bar control on a form or text box to indicate the position of the current record in a recordset.

VBA Code

Private Sub cmdPercentPosition_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim rst As Recordset
   Dim strSearch As String
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset("Employees", dbOpenSnapshot)
   strSearch = "[Title] = 'Sales Rep'"
   
   'MoveLast to ensure an accurate count of records.
   With rst
      .MoveLast
      .MoveFirst
      .FindFirst strSearch
      Debug.Print !LastName & " record -- " & .PercentPosition & "%"
      .FindNext strSearch
      Debug.Print !LastName & " record -- " & .PercentPosition & "%"
      .Close
   End With
   dbs.Close
 
End Sub

RecordCount

Data Type

Long

Description

Returns the number of records in a recordset. In case of dynaset-, snapshot-, or forward-only-type recordsets, you need to access all the records in the recordset before getting an accurate count of the records. See the following code sample for an example of usage of RecordCount for this purpose. This is not necessary for table-type recordsets.

VBA Code

Private Sub cmdRecordCount_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim rst As Recordset
   Dim intCount As Integer
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset("Employees", dbOpenSnapshot)
   
   Debug.Print "Record count before traversing recordset: " & _
               rst.RecordCount
   
   'MoveLast to ensure an accurate count of records.
   rst.MoveLast
   
   Debug.Print "Record count after traversing recordset: " & _
               rst.RecordCount
   rst.Close
   dbs.Close
 
End Sub

RecordStatus

Data Type

Long

Description

Indicates the update status of the current record, if it is part of a batch update (for ODBCDirect workspaces only). The value returned indicates whether (and how) the current record will be involved in the next optimistic batch update. See Table 8-9 for a listing of the constants that may be returned.

Table 8-9: The RecordStatus Return Value Intrinsic Constants

Named Constant	Value	Description
dbRecordUnmodified	0	(Default) The record has not been modified or has been updated successfully.
dbRecordModified	1	The record has been modified and not updated in the database.
dbRecordNew	2	The record has been inserted locally with the AddNew method, but not yet inserted into the database.
dbRecordDeleted	3	The record has been deleted locally, but not yet deleted in the database.
dbRecordDBDeleted	4	The record has been deleted locally and in the database.

Restartable

Data Type

Boolean

Description

Indicates whether a recordset supports the Requery method. If the value is True, Requery can be used to re-execute the query on which the recordset is based; if it is False, the query can't be re-executed.

VBA Code

The following code opens three different types of recordsets, examines the Restartable property of each, and requeries those that are restartable:

Private Sub cmdRestartable_Click()
 
   Dim dbs As Database
   Dim rstTable As Recordset
   Dim rstQuery As Recordset
   Dim rstSQL As Recordset
   Dim rst As Recordset
   Dim strDBName As String
   Dim intCount As Integer
   Dim strTable As String
   Dim strQuery As String
   Dim strSQL As String
 
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   strTable = "Orders"
   strQuery = "Ten Most Expensive Products"
   strSQL = "SELECT * FROM Customers"
   Set rstTable = dbs.OpenRecordset(strTable, dbOpenTable)
   Set rstSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
   Set rstQuery = dbs.OpenRecordset(strQuery, dbOpenSnapshot)
   
   'Determine whether each recordset is restartable,
   'and requery it if so.
   For Each rst In dbs.Recordsets
      Debug.Print rst.Name & " restartable? " & rst.Restartable
      If rst.Restartable = True Then rst.Requery
   Next rst
   
End Sub

Sort

---

Data Type

String

Description

Sets or returns the sort order for records in a recordset (for Jet workspaces only). Basically, it is the ORDER BY clause of a SQL statement without the phrase ORDER BY. You can use Sort with dynaset- and snapshot-type recordsets only. As with the Filter property, the Sort property only takes effect when a new recordset is created from the sorted recordset. The Sort property overrides any sort order that might be specified for a QueryDef on which a recordset is based.

TIP:  

Using the Sort property on a recordset is generally less efficient than just applying a sort order to a recordset and opening it in one step, using a SQL statement with an ORDER BY clause.

VBA Code

The following code applies a Sort order to a recordset and then opens a second recordset based on the sorted original recordset:

Private Sub cmdSort_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim rst As Recordset
   Dim rstSort As Recordset
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
   rst.Sort = "Region"
   Set rstSort = rst.OpenRecordset
   
   With rstSort
       Do Until .EOF
           Debug.Print "State: " & !Region & " for "; !LastName
           .MoveNext
       Loop
   End With
 
End Sub

StillExecuting

Data Type

Boolean

Description

For ODBCDirect workspaces only, indicates whether an asynchronous operation (one called with the dbRunAsync option) has finished executing. The return value is True if the query is still executing and False if it has finished executing. The Cancel method can be called to cancel execution if the value is True.

Transactions

Data Type

Boolean

Description

This property is True if the recordset supports transactions, and False if it does not. For ODBC workspaces the Transactions property indicates whether the ODBC driver supports transactions. The property can be used for dynaset- or table-type recordsets in Jet workspaces; for snapshot- and forward-only-type recordsets in Jet workspaces, it is always False. For dynaset- or table-type recordsets in Jet workspaces, the Transactions property is always True, indicating that you can use transactions.

TIP:  

You should always check the Transactions property and make sure it returns True before working with transactions using the BeginTrans, CommitTrans, and Rollback methods.

VBA Code

This code creates a recordset from the QueryDefs in Northwind and lists the Transactions value for each one:

Private Sub cmdTransactions_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim qdf As QueryDef
   Dim rst As Recordset
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   
   Debug.Print "QueryDefs in " & dbs.Name
   For Each qdf In dbs.QueryDefs
      On Error Resume Next
      Set rst = dbs.OpenRecordset(qdf.Name)
      Debug.Print "Recordset name and type: " & rst.Name & vbTab & _
                  rst.Type
      Debug.Print "Transactions possible?: " & rst.Transactions
   Next qdf
   dbs.Close
  
End Sub

Type

Data Type

Integer

Description

Indicates the recordset type of a Recordset object. The possible values are listed in Table 8-10.

Table 8-10: The Type Setting Return Values Intrinsic Constants

Named Constant	Value	Description
dbOpenTable	1	Table ( Jet workspaces only)
dbOpenDynamic	16	Dynamic (ODBC workspaces only)
dbOpenDynaset	2	Dynaset
dbOpenSnapshot	4	Snapshot
dbOpenForwardOnly	96	Forward-only

Using the Type property on recordsets can be useful when you don't know what type the recordset is. Note that the recordset type is not the same as the QueryDef type. There are many more QueryDef type constants than Recordset constants, and the available constants are different for the two types of objects.

VBA Code

The following code sample lists the recordset type of recordsets based on all the queries in Northwind:

Private Sub cmdType_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim qdf As QueryDef
   Dim rst As Recordset
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   
   Debug.Print "QueryDefs in " & dbs.Name
   For Each qdf In dbs.QueryDefs
      Debug.Print "Query name and type: " & qdf.Name & vbTab & qdf.Type
      Set rst = dbs.OpenRecordset(qdf.Name)
      Debug.Print "Recordset name and type: " & rst.Name & vbTab & _
                  rst.Type
   Next qdf
   dbs.Close
 
End Sub

Updatable

Data Type

Boolean

Description

Indicates whether a recordset can be changed or updated. If it is True, the recordset can be updated; if it is False, it can't be updated.

VBA Code

This code creates recordsets from all the Northwind queries and reports on whether they are updatable:

Private Sub cmdUpdatable_Click()
 
   Dim dbs As Database
   Dim strDBName As String
   Dim qdf As QueryDef
   Dim rst As Recordset
   
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   
   Debug.Print "QueryDefs in " & dbs.Name
   For Each qdf In dbs.QueryDefs
      On Error Resume Next
      Set rst = dbs.OpenRecordset(qdf.Name)
      Debug.Print "Recordset name and type: " & rst.Name & vbTab & _
                  rst.Type
      Debug.Print "Recordset updatable?: " & rst.Updatable
   Next qdf
   dbs.Close
   
End Sub

UpdateOptions

---

Data Type

Long

Description

Indicates how the WHERE clause is constructed for each record during a batch update and whether the update should use an UPDATE statement or a DELETE followed by an INSERT (for ODBCDirect workspaces only). The UpdateOptions value can be any of the constants in Table 8-11.

Table 8-11: The UpdateOptions Return Value/Settings Intrinsic Constants

Named Constant	Value	Description
dbCriteriaKey	1	(Default) Uses just the key column(s) in the where clause.
dbCriteriaModValues	2	Uses the key column(s) and all updated columns in the where clause.
dbCriteriaAllCols	4	Uses the key column(s) and all the columns in the where clause.
dbCriteriaTimeStamp	8	Uses just the timestamp column if available (will generate a run-time error if no timestamp column is in the result set).
dbCriteriaDeleteInsert	16	Uses a set of DELETE and INSERT statements for each modified row.
dbCriteriaUpdate	32	(Default) Uses an UPDATE statement for each modified row.

ValidationRule

Data Type

String

Description

Returns a value used to validate data as it is being changed or added to a field in a recordset's underlying data source table. It only applies to Jet workspaces. The ValidationRule phrase describes a comparison in the form of a SQL WHERE clause without the WHERE keyword. If the data does not meet the validation criteria, a trappable run-time error is generated, and the error message contains the text of the ValidationText property, if specified, or else the text of the expression specified by the ValidationRule property. See the ValidationRule section in Chapter 10, TableDefs Collection and TableDef Object, for more information on this property.

TIP:  

ValidationRule comparison strings are limited to referencing the field; they can't contain references to user-defined functions or queries.

VBA Code

This code lists the fields in the Northwind Customers table and their validation rules and validation text, if any:

Private Sub cmdValidationRule_Click()
   
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
   Dim fld As Field
   
   strTable = "Employees"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
   With rst
      .MoveLast
      For Each fld In .Fields
         Debug.Print fld.Name
         If fld.ValidationRule <> "" Then
            Debug.Print "Validation Rule: " & fld.ValidationRule
            Debug.Print "Validation Text: " & fld.ValidationText
         End If
      Next fld
      .Close
   End With
 
End Sub

ValidationText

Data Type

String

Description

The ValidationText property returns a value specifying the text of the message that appears when data for a field fails the validation rule specified by the ValidationRule property. It applies only to Jet workspaces. See the ValidationText section in Chapter 10 for more information on this property. See the code sample in the ValidationRule section for an example of usage of this property.

Recordset Object Methods

Table 8-12 summarizes the Recordset type supported by each method and whether it applies to Jet ( J) or ODBCDirect (O) workspaces, or both ( JO). A blank cell indicates that the property does not apply to either type of workspace.

Table 8-12: Recordset Method Summary

Method	Table	Dynaset	Snapshot	Forward-Only	Dynamic
AddNew	J	JO	O[2]	O	O
Cancel		O	O	O	O
CancelUpdate	J	JO	Oa	O	O
Clone	J	J	J
Close	J	JO	JO	JO	O
CopyQueryDef		J	J	J
Delete	J	JO	Oa	O	O
Edit	J	JO	Oa	O	O
FillCache		J
FindFirst		J	J
FindLast		J	J
FindNext		J	J
FindPrevious		J	J
GetRows	J	JO	JO	JO	O
Move	J	JO	JO	[3]	O
MoveFirst	J	JO	JO		O
MoveLast	J	JO	JO		O
MoveNext	J	JO	JO	JO	O
MovePrevious	J	JO	JO		O
NextRecordset		O	O	O	O
OpenRecordset	J	J	J
Requery		JO	JO	JO	O
Seek	J
Update	J	JO	Oa	O	O

The recordset types and their uses are listed in Table 8-13.

Table 8-13: Recordset Types and Their Uses

Recordset Type	Usage
Table	Represents a single base table. Supports the AddNew, Delete, and Seek methods, but not the Find* methods. ( Jet only.)
Dynamic	Represents one or more base tables. Supports the AddNew and Delete methods, but not the Find* or Seek methods.
Dynaset	Represents a table or an updatable query. Supports the AddNew, Delete, and Find* methods, but not the Seek method.
Snapshot	A read-only recordset; useful for finding data or printing. Does not allow updating, except in the case of an updatable Snapshot in an ODBCDirect workspace. Supports the Find* methods.
Forward-only	Similar to a snapshot, but only allows forward movement. Useful when you only need to make a single pass through a recordset. Does not support the Find* methods.

AddNew

---

recordset.AddNew

Adds a new record to an updatable recordset (table-type or dynaset recordsets only). For dynasets the new records are added at the end of the recordset. For indexed dynasets, the new record is placed in indexed order; if the dynaset is not indexed, the new record is added to the end of the recordset.

TIP:  

Don't confuse AddNew and Append. The DAO AddNew method is the equivalent of Append in other database languages, such as dBASE, while the Append method in DAO is used to add new members to collections.

After adding a new record with the AddNew method, you need to use the Update method to save the new record, as in the following code sample. If you omit the Update, you won't get a warning, and the new record will be lost when you move to another record or close the recordset.

Note that in VBA code, you should use the dot (.) operator for recordset methods and properties, and the bang (!) operator for fields. In VBS code use the dot operator for methods, properties, and fields.

TIP:  

If you get an "Item not found in this collection" error message when updating a recordset, it is probably the result of a misspelled field name (fields are members of the Fields collection within the recordset).

VBA Code

This code first lists the categories in the Northwind Categories table, then adds a new record, then lists the categories again, showing the newly added one:

Private Sub cmdAddNew_Click()
 
   Dim dbs As Database
   Dim rst As Recordset
   Dim strDBName As String
   Dim strTable As String
 
   strTable = "Categories"
   strDBName = "D:\Documents\Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
 
   'List categories before adding new record
   Debug.Print "Categories before adding new record:" & vbCrLf
   rst.MoveFirst
   Do Until rst.EOF
      Debug.Print rst!CategoryName
      rst.MoveNext
   Loop
   
   'Add new category
   With rst
      .AddNew
      !CategoryName = "Dried Foods"
      !Description = _
         "Freeze-dried and sun-dried fruits, vegetables, and meats"
      .Update
   End With
   
   'List categories after adding new record
   Debug.Print vbCrLf & "Categories after adding new record:" & vbCrLf
   rst.MoveFirst
   Do Until rst.EOF
      Debug.Print rst!CategoryName
      rst.MoveNext
   Loop
   rst.Close
 
End Sub

Cancel

---

recordset.Cancel

Cancels execution of a pending asynchronous method call. It only applies to recordsets in ODBCDirect workspaces since it requires use of the dbRunAsync value for the MoveLast method's Options argument. See the StillExecuting section for an example that uses this method.

CancelUpdate

---

recordset.CancelUpdate updatetype

Argument	Data Type	Description
recordset	Recordset object	The Recordset object for which you are canceling pending updates
updatetype	Integer	A named constant or Integer value (see Table 8-14)

Cancels any pending updates for a Recordset object, such as would result from use of the Edit or AddNew methods. Before using the CancelUpdate method, you should check the EditMode property of the recordset to find out if there is a pending operation that can be canceled.

Table 8-14: The UpdateType Intrinsic Constants

Named Constant	Value	Description
dbUpdateRegular	1	(Default) Cancels pending changes that aren't cached
dbUpdateBatch	4	Cancels pending changes in the update cache

TIP:  

The type argument settings in