Microsoft^® Jet Database Engine Programmer's Guide, Second Edition

Using Recordset Objects

You use Recordset objects to manipulate the data in a database at the record level. There are five types of Recordset objects -- table, dynaset, snapshot, forward-only, and dynamic. Table-type Recordset objects are available only in Microsoft Jet workspaces. Dynamic-type Recordset objects are available only in ODBCDirect workspaces. You specify the type of recordset you want to open by using the type argument of the OpenRecordset method.

• A table-type Recordset object refers to either a local table in the current database, or to a linked table in an external database created with Microsoft Access or Microsoft Jet. When you create a table-type Recordset object, the database engine opens the actual table, and your subsequent data manipulations operate directly on table data. A table-type Recordset object can be opened against only one table; it cannot be opened against a join or union query.
• One of the biggest advantages of the table-type Recordset object is that it can be indexed by using an index created for the underlying table. This allows much faster sorting and filtering than is possible with the other Recordset object types. To locate specific records, use the Seek method, which is faster than the Find methods.
• A dynaset-type Recordset object refers to either local or linked tables, or to the result of a query. It's actually a set of references to records in one or more tables. With a dynaset, you can extract and update data from more than one table, including linked tables from other databases. Heterogeneous joins are a unique feature of dynasets -- they enable you to use updatable join queries against tables in different types of databases.
• One of the main benefits of the dynaset-type Recordset object is that a dynaset and its underlying tables update each other. Changes made to records in the dynaset are written to the underlying table, and changes made by other users to data in the underlying tables while the dynaset is open are reflected in the dynaset. The dynaset is the most flexible and powerful type of Recordset object, although running queries and manipulating data may take longer than they would with a table-type Recordset object.
• A snapshot-type Recordset object contains a static copy of the data as it exists at the time the snapshot is created. In a Microsoft Jet workspace, a snapshot can't be updated. In an ODBCDirect workspace, a snapshot may be updatable, depending on the Open Database Connectivity (ODBC) driver. For more information about ODBCDirect, see Chapter 8, "Accessing External Data," and Chapter 9, "Developing Client/Server Applications."

The main advantage of using a snapshot is that it creates less processing overhead than the other types, so it may execute queries and return data faster, especially when you are working with ODBC data sources. Note that for .mdb files, Long Binary (OLE Object) and Memo fields are represented in a snapshot by pointers, rather than the actual data.

• A forward-only-type Recordset object is identical to a snapshot-type Recordset object but you can only scroll forward through its records. A forward-only-type Recordset object can't be updated. Using this type of Recordset object improves performance when you need to make only a single pass through the records.

Figure 5.1 illustrates the difference between how data is stored in a snapshot-type or forward-only-type Recordset object and how it's stored in a dynaset. The snapshot stores a copy of the entire record (except for Memo and Long Binary fields). The dynaset stores just the primary key for each record, copying the full record only when it's needed for editing or display purposes.

Figure 5.1 The difference between records stored in dynaset- and snapshot-type or forward-only-type Recordset objects.

<img1>

• A dynamic-type Recordset object is available only in ODBCDirect workspaces. This Recordset object represents a query result set in which you can add, change, or delete records. In addition, changes made to data in the underlying tables by other users are reflected in the Recordset object.

The type of Recordset object you use depends on what you want to do and whether you want to change or simply view the data. For example, if you must sort the data or work with indexes, use a table-type Recordset object. Because they are indexed, table-type Recordset objects also provide the fastest way to locate data. For example, you can use the Seek method to quickly locate records in an indexed table-type Recordset object only. To search for records in other Recordset object types, you must use the FindFirst, FindLast, FindNext, or FindPrevious method. If you want to be able to update a set of records selected by a query, use a dynaset. If the table-type is unavailable in a specific situation and you only need to scan through a set of records, using a snapshot-type or a forward-only-type Recordset object may improve performance.

Using a table-type Recordset object, if that type is available, almost always results in the best performance.

Creating a Recordset Variable

The OpenRecordset method is the basic method of creating a Recordset object variable. To use it, you first declare a variable of type Recordset object, and then set the variable to the object returned by the OpenRecordset method.

The OpenRecordset method is available from Database, TableDef, QueryDef and existing Recordset objects. For ODBCDirect workspaces, the OpenRecordset method is available from the Connection, Database, and QueryDef objects. The syntax of the OpenRecordset method for Database and Connection objects is:

Set recordset = object.OpenRecordset (source [, type [, options [, lockedits]]])

The syntax of the OpenRecordset method for all other types of objects is:

Set recordset = object.OpenRecordset ([type [, options [, lockedits]]])

The recordset argument is the name of the new Recordset object.

The object argument is the object from which you're creating the new Recordset object. Microsoft Jet uses the source argument only for Recordset objects created from Database or Connection objects.

The source argument is an existing TableDef or QueryDef object in the database or a valid record-returning SQL query or statement. For TableDef, QueryDef, and Recordset objects, the object itself is automatically used as the source.

The type argument is an intrinsic constant that specifies the kind of Recordset object you want to create. The available type constants are:

• dbOpenTable
• dbOpenDynamic
• dbOpenDynaset
• dbOpenSnapshot
• dbOpenForwardOnly

If you open a Recordset object in a Microsoft Jet workspace and you don't specify a type, OpenRecordset creates a table-type Recordset object, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type Recordset object. In an ODBCDirect workspace, the default setting is dbOpenForwardOnly.

With the options argument of the OpenRecordset method, you can specify a number of other features for a Recordset object by using the following constants:

• dbAppendOnly With this constant, you can only append new records to the Recordset object; you cannot edit or delete existing records. This is useful in applications that collect and archive data (dynaset only).
• dbSeeChanges If another user changes data in a record on which this Recordset object has invoked the Edit method, but before it has invoked the Update method, a run-time error will occur. This is useful in applications where multiple users have simultaneous read/write access to the same data (dynaset only).
• dbDenyWrite When used on a dynaset or snapshot, this constant prevents other users from opening a table or dynaset-type Recordset object, although they can still open snapshot- or forward-only-type Recordset objects (Microsoft Jet workspaces only).
• dbDenyRead Prevents other users from reading data in the table (table only).
• dbReadOnly Prevents your Recordset object from modifying data. This is useful where the Recordset object will be used to display data in a form that otherwise allows data entry. The dbReadOnly constant in the lockedits argument replaces this constant, which is provided only for backward compatibility (Microsoft Jet workspaces only).
• dbForwardOnly Creates a forward-only snapshot. Note that Recordset objects created with this option cannot be cloned and only support the Move and MoveNext methods to move directly through the records (snapshot only). This constant is provided for backward compatibility only. To create a forward-only-type Recordset object, you should use the dbOpenForwardOnly constant in the type argument instead of in this argument.
• dbSQLPassThrough Where the source argument is an SQL statement, use this constant to pass the SQL statement to an ODBC data source for processing. If used with a dynaset, data is not updatable (Microsoft Jet snapshot only).
• dbConsistent (Default) Only consistent updates are allowed (Microsoft Jet dynaset and snapshot only).
• dbInconsistent Inconsistent updates are allowed. Opposite of dbConsistent (Microsoft Jet dynaset and snapshot only).

• dbRunAsync Runs an asynchronous query (ODBCDirect workspaces only).
• dbExecDirect Runs a query that skips SQLPrepare and directly calls SQLExecDirect. Use this constant only when you're not opening a Recordset object based on a parameter query (ODBCDirect workspaces only).

With the lockedits argument of the OpenRecordset method, you can specify locking options for a Recordset object by using the following constants:

• dbReadOnly Prevents users from making changes to the Recordset object. You can use this constant in either the options argument or the lockedits argument, but not both. If it is used in both arguments, an error occurs. This setting is the default in ODBCDirect workspaces.
• dbPessimistic Uses pessimistic locking to determine how changes are made to a Recordset object in a multiuser environment. With pessimistic locking, the page that contains the record being edited is locked as soon as the Recordset object's Edit method is invoked. This setting is the default in Microsoft Jet workspaces.
• dbOptimistic Uses optimistic locking to determine how changes are made to a Recordset object in a multiuser environment. With optimistic locking, the page that contains the record being edited is not locked until the Recordset object's Update method is invoked.
• dbOptimisticValue Uses optimistic concurrency based on record values (ODBCDirect workspaces only).
• dbOptimisticBatch Enables batch optimistic updating (ODBCDirect workspaces only).

Creating a Recordset Object from a Table

The method you use to create a Recordset object from a table depends on whether the table is local to the current database or is a linked table in another database. The following discussion explains the differences and provides examples for each type of table.

From a Table in a Local Microsoft Jet Database

The following example uses the OpenRecordset method to create a table-type Recordset object from a table, where strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath, dbOpenTable)
Set rst = dbs.OpenRecordset("Customers")
  

From a Linked Table in a Different Database Format

The next example creates a dynaset-type Recordset object for a linked Paradox 3.x table. Because the table type is not available when you open a Recordset object from a linked table in a non-Microsoft Jet database, Microsoft Jet selects the next most efficient type, opening a dynaset-type Recordset object. In the following example, strDbPath is the path to the Microsoft Jet database:

Dim dbs As Database
Dim tdf As TableDef, rst As Recordset
' Get current database.
Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.CreateTableDef("PDXAuthor")
' Connect to Paradox table Author in database C:\JetBook\Samples\pdx.
tdf.Connect = "Paradox 3.X;DATABASE=C:\JetBook\Samples\pdx"
tdf.SourceTableName = "Author.db"
' Link table.
dbs.TableDefs.Append tdf
' Create a dynaset-type Recordset object for the table.
Set rst = tdf.OpenRecordset()
' Print each author's first and last name.
With rst
 Do While Not .EOF
  Debug.Print !au_fname & " " & !au_lname
  .MoveNext
 Loop
End With

You can also open a Paradox table as a table-type Recordset object by first opening the Paradox database.

Using an Index on a Table-Type Recordset Object

You can order records in a table-type Recordset object by setting its Index property. Any Index object in the Indexes collection of the Recordset object's underlying table can be specified in the Index property.

The following example illustrates how to create a table-type Recordset object based on the Customers table that uses using an existing index called City. Note how setting the Index property re-sorts the records in the Recordset object. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Customers", dbOpenTable)
With rst
 .MoveFirst    ' Move to first record.
 MsgBox !CompanyName ' Show first CompanyName field with no index set.
 .Index = "City"   ' Select existing City index to reorder records.
 .MoveFirst    ' Move to new first record.
 MsgBox !CompanyName ' Show new first CompanyName field.
End With

You can use the Seek method to locate a record in an indexed table-type Recordset object. You must set the Index property before using the Seek method. If you set the Index property to an index that doesn't exist, a trappable run-time error occurs. If you want to sort records according to an index that doesn't exist, either create the index first, or create a dynaset- or snapshot-type Recordset object using a query with an ORDER BY clause that returns records in a specified order.

Creating a Recordset Object from a Query

You can also create a Recordset object based on a stored SELECT query. In the following example, Current Product List is an existing SELECT query stored in the NorthwindTables database, and strDbPath is the path to the database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Current Product List")

The OpenRecordset method also accepts an SQL string instead of the name of an existing query. The previous example can be rewritten as follows:

Dim dbs As Database, rst As Recordset
Dim strSQL As String
Set dbs = OpenDatabase(strDbPath)
strSQL = "SELECT ProductID, ProductName FROM Products " & _
 "WHERE Discontinued=False ORDER BY ProductName;"
Set rst = dbs.OpenRecordset(strSQL)
  

The disadvantage of this approach is that the query must be compiled each time it's executed, whereas the stored query is compiled when it's saved, which usually results in slightly better performance.

Default Recordset Object Types

Because Microsoft Jet automatically chooses the default Recordset object type depending on the data source and how the Recordset object is opened, you don't need to specify a Recordset object type. However, you can force Microsoft Jet to create specific Recordset objects by specifying the type argument of the OpenRecordset method.

The following list describes the available types and the default type, depending on how you open the Recordset object:

Set rst = dbs.OpenRecordset(datasource)

If datasource is a table local to the database, all Recordset object types except the dynamic type are available, and the default type is table. The dynamic-type Recordset object is available only in ODBCDirect workspaces. If datasource is not a local table, only the dynaset, snapshot, or forward-only Recordset object types are available, and dynaset is the default.

Set rst = tdf.OpenRecordset()

If tdf refers to a table in a Microsoft Jet database or to an external ISAM database opened directly, all Recordset object types except the dynamic type are available and table is the default type. If tdf is in an ODBC database or is a linked table in an external database, only the dynaset, snapshot, and forward-only Recordset object types are available and dynaset is the default.

Set rst = qdf.OpenRecordset()

Only the dynaset, snapshot, and forward-only Recordset object types are available, and the default is dynaset.

Set rstNew = rstExisting.OpenRecordset()

Only the dynaset, snapshot, and forward-only Recordset object types are available. The default is the Recordset object type of the source Recordset object. For example, in the preceding example, if rstExisting is a snapshot-type Recordset object, rstNew will also be a snapshot-type Recordset object.

Microsoft Access Users To create a Recordset object based on a Microsoft Access form, you can use the RecordsetClone property of the form. This creates a dynaset-type Recordset object that refers to the same underlying query or data as the form's RecordSource property. If a form is based on a query, for example, referring to the form's RecordsetClone property is the equivalent of creating a dynaset with the same query. You can use the RecordsetClone property when you want to apply a method that can't be used with forms, such as the FindFirst method. The RecordsetClone property provides access to all the methods and properties that you can use with a dynaset-type Recordset object. The syntax for the RecordsetClone property is:

Set recordset = form.RecordsetClone

The form argument is the name of an open Microsoft Access form.

The following example returns a Recordset object variable from the record source of a form. It then searches for the specified record in the recordset, and if the record exists, the form's Bookmark property is set to the value of the recordset's Bookmark property. Setting the form's Bookmark property displays the corresponding record.

In this example, strFormName is the name of a form, strFieldName is the name of a field on a form, and strFieldValue is the value to search for in the recordset.

Sub FindFormRecord(strFormName As String, strFieldName As String, _
 strFieldValue As String)
 Dim rst As Recordset
 Dim frm As Form
 
 ' Open the specified form.
 DoCmd.OpenForm strFormName
 Set frm = Forms(strFormName)
 ' Open a recordset based on the form's RecordSource property.
 Set rst = frm.RecordsetClone
 With rst
  ' Search for the specified value.
  .FindFirst strFieldName & " Like """ & strFieldValue & "*" & """"
  ' If value is not found, display message.
  If .NoMatch Then
   MsgBox "No record found for " & strFieldName & ": " & strFieldValue
  ' If value is found, set form's Bookmark property to value of recordset's
  ' Bookmark property.
  Else
   frm.Bookmark = .Bookmark
  End If
  .Close
 End With
End Sub

The RecordsetClone property always creates the type of Recordset object being cloned (the type of Recordset object on which the form is based); no other types are available.

Visual Basic Users You can create a Recordset object from a Data control by setting a new Recordset object variable equal to the value of the control's Recordset object property. For more information, see the Microsoft Visual Basic Programmer's Guide.

Sorting and Filtering Records

Unless you open a table-type Recordset object and set its Index property, you can't be sure records will appear in any specific order. Most of the time, however, you want to retrieve records in some specific order. For example, you may want to view invoices arranged by increasing invoice number or retrieve employee records arranged alphabetically by last name.

To sort non-table Recordset object data, use an ORDER BY clause in the SQL query that constructs the Recordset object, whether that clause is contained in a QueryDef object, a stored query in a database, or in an SQL string passed to the OpenRecordset method.

With any type of Recordset object, use an SQL WHERE clause in a query to filter data (to restrict the result set to records that meet some criteria).

The following example opens a dynaset-type Recordset object, and uses an SQL statement to filter and sort a Recordset object by using the WHERE and ORDER BY clauses. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("SELECT FirstName, " & _
 "LastName FROM Employees WHERE Title = ""Sales Representative"" " & _
 "ORDER BY LastName")
With rst
 Do While Not .EOF
  Debug.Print !FirstName & " " & !LastName
  .MoveNext
 Loop
End With

Microsoft Access Users One drawback of executing an SQL query in an OpenRecordset method is that it has to be compiled every time you run it. If this query is to be used frequently, you can improve performance by creating a stored query that contains the same SQL statement, and then opening a Recordset object against the query. In the following example, Sales Representatives is a saved query:

Dim dbs As Database, rst As RecordsetSet 
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Sales Representatives")

For even greater flexibility and control at run time, you can use query parameters to determine the sort order and filter criteria. This is discussed in more detail in the "Using Parameter Queries" section later in this chapter.

Re-creating a Query from a Recordset Object

A Recordset object opened from a QueryDef object can also be used as a template to re-create the QueryDef object using the CopyQueryDef method. This is useful in situations where a Recordset object variable created from a QueryDef is passed to a function, and the function must re-create the SQL equivalent of the query and possibly modify it.

Modifying a Query from a Recordset Object

You can use the Requery method on any Recordset object except a table-type Recordset object when you want to re-execute the underlying query after changing a parameter. This is more convenient than opening a new Recordset object, and the query also executes faster.

The following procedure takes a Recordset object that was opened on a QueryDef object and uses the CopyQueryDef method to extract the underlying query's SQL string, prompts the user to add an additional WHERE clause to the query, and requeries the Recordset object based on the altered query:

Function AddQueryFilter(rst As Recordset) As Recordset
 Dim qdf As QueryDef
 Dim strNewFilter As String, strQuery As String
 Dim varPosition As Variant, intLength As Integer
 Const conOperationNotSupported As Integer = 3251
 On Error GoTo Err_AddQueryFilter
 Set qdf = rst.CopyQueryDef
 strNewFilter = InputBox("Enter new constraint for WHERE clause, " _
  & "e.g., ProductName Like 'M*'")
 ' Create new query string with added restriction.
 strQuery = qdf.SQL
 ' Return string length.
 intLength = Len(strQuery)
 ' Check for WHERE clause in string.
 varPosition = InStr(strQuery, "WHERE")
 If varPosition > 0 Then
  ' Append new restriction to existing WHERE clause.
  strQuery = Left(strQuery, varPosition + 5) & strNewFilter & " AND " _
   & Right(strQuery, intLength - (varPosition + 5))
  qdf.SQL = strQuery
 Else
  ' If no WHERE clause, alert user and exit function.
  ' You could also add code to handle this situation.
  MsgBox "The SQL statement you supplied did not include a WHERE clause."
  GoTo Exit_AddQueryFilter
 End If
  
 ' Requery recordset.
 rst.Requery qdf
 Set AddQueryFilter = rst
 
Exit_AddQueryFilter:
 Exit Function
Err_AddQueryFilter:
 If Err = conOperationNotSupported Then
  Dim strMsg As String
  strMsg = "This recordset was not opened on a QueryDef object. " & _
   "You can call this function only with a recordset opened " & _
   "on a QueryDef object."
  MsgBox strMsg, vbOKOnly
 Else
  MsgBox Err & ": " & vbCrLf & Err.Description
 End If
 Set AddQueryFilter = Nothing
 Resume Exit_AddQueryFilter
End Function

The Sort and Filter Properties

Another approach to sorting and filtering recordsets is to set the Sort and Filter properties on an existing Recordset object, and then open a new Recordset object from the existing one. However, this is usually much slower than just including the sort and filter criteria in the original query or changing the query parameters and re-executing the query with the Requery method. The Sort and Filter properties are useful when you want to allow a user to sort or restrict a result set but the original data source is unavailable for a new query -- for example, when a Recordset object variable is passed to a function, and the function must reorder records or restrict the records in the set. With this approach, performance is likely to be slow if more than 100 records are in the Recordset object. Using the CopyQueryDef method described in the previous section is preferable.

Moving Through a Recordset

A Recordset object has a current position, usually at a record. When you refer to the fields in a Recordset object, you obtain values from the record at the current position, which is known as the current record. However, the current position can also be immediately before the first record in a Recordset object or immediately after the last record. In certain circumstances, the current position is undefined. When you open a Recordset object that contains at least one record, the first record is the current record and the Recordset object's BOF property is False. If the Recordset object contains no records, the Recordset object's BOF and EOF properties will both be True. For more information about the BOF and EOF properties, see the section "Detecting the Limits of a Recordset."

You can use the Move methods to loop through the records in a Recordset object:

The following example changes the job title of all sales representatives in a table called Employees. After opening the table, the code sets the index, which makes it necessary to use the MoveFirst method to locate the first record. For each record satisfying the title condition, the example changes the title and saves the change with the Update method. It uses the MoveNext method to move to the next record. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
With rst
 ' Set current index.
 .Index = "LastName"
 ' Locate first record.
 .MoveFirst
 ' Begin loop.
 Do Until .EOF
  If !Title = "Sales Representative" Then
   ' Enable editing.
   .Edit
   ' Change title.
   !Title = "Account Executive"
   ' Save changes.
   .Update
  End If
 ' Locate next record.
 .MoveNext
 ' End of loop.
 Loop
 ' Close recordset.
 .Close
End With

Visual Basic Users You can use the Data control to browse and display records in a Recordset object and to edit data in records as they are displayed. For more information on the Data control, see the Microsoft Visual Basic Programmer's Guide.

Detecting the Limits of a Recordset

In a Recordset object, if you try to move too far in a direction, a run-time error occurs. For example, if you try to use the MoveNext method when you're already at the end of the Recordset object, a trappable error occurs. For this reason, it's helpful to know the limits of the Recordset object.

The BOF property indicates whether the current position is at the beginning of the Recordset object. If BOF is True, the current position is before the first record in the Recordset object. The BOF property is also True if there are no records in the Recordset object when it's opened. Similarly, the EOF property is True if the current position is after the last record in the Recordset object or if there are no records in the Recordset object.

The following example shows how to use the BOF and EOF properties to detect the beginning and end of a Recordset object. This code fragment creates a table-type Recordset object based on the Employees table. It moves through the records, first from the beginning of the Recordset object to the end, and then from the end of the Recordset object to the beginning. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
With rst
 ' Move to first record in recordset.
 .MoveFirst
 Do Until .EOF
  ' Print value of data.
  Debug.Print rst!LastName
  ' Move to next record.
  .MoveNext
 Loop
 Debug.Print
 ' Move to last record.
 .MoveLast
 Do Until .BOF
  Debug.Print rst!LastName
  ' Move to previous record.
  .MovePrevious
 Loop
 .Close
End With

Note that there's no current record immediately following the first loop. The BOF and EOF properties both have the following characteristics:

• If the Recordset object contains no records when you open it, both BOF and EOF are True.
• When BOF or EOF are True, they remain True until you move to an existing record, at which time the value of BOF or EOF becomes False.
• When BOF or EOF is False, and the only record in a Recordset object is deleted, the property remains False until you attempt to move to another record, at which time both BOF and EOF become True.
• At the moment you create or open a Recordset object that contains at least one record, the first record is the current record, and both BOF and EOF are False.
• If the first record is the current record and you use the MovePrevious method to move past the first record in the Recordset object, BOF is set to True. If you use the MovePrevious method while BOF is True, a run-time error occurs. When this happens, BOF remains True and there is no current record.
• Similarly, moving past the last record in the Recordset object changes the value of the EOF property to True. If you use the MoveNext method while EOF is True, a run-time error occurs. When this happens, EOF remains True and there is no current record.

Figure 5.2 Relationship between the current record position and the BOF and EOF properties.

<IMG 2>

Counting the Number of Records in a Recordset

You may want to know the number of records in a Recordset object. For example, you may want to create a form that shows how many records are in each of the tables in a database. Or you may want to change the appearance of a form or report based on the number of records it includes.

The RecordCount property for a table-type Recordset object contains the number of records in the table. The RecordCount property for any other type of Recordset object contains the number of records that have been actually been visited. A Recordset object with no records has a RecordCount property value of 0.

The RecordCount property value equals the number of records actually visited. For example, when you first create a non-table-type Recordset object, you have accessed (or "visited") only the first record. If you check the RecordCount property immediately after creating this Recordset object (assuming it has at least one record), the value is 1. To visit all the records, use the MoveLast method immediately after opening the Recordset object, then use MoveFirst to return to the first record. This is not done automatically, because it may be slow, especially for large result sets. Opening a table-type Recordset object effectively visits all of the records in the underlying table, and RecordCount totals the number of records in the table as soon as the Recordset object is opened. Aborted transactions may make the RecordCount property value out of date in some multiuser situations. Compacting the database restores the table's record count to the correct value.

The following example creates a dynaset-type Recordset object (because it's based on an SQL statement) from the Employees table, and then determines the number of records in the Recordset object. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("SELECT LastName, " _
 & "FirstName FROM Employees")
With rst
 .MoveLast
 MsgBox "There are " & .RecordCount & " records in this recordset."
End With

As records in a dynaset-type Recordset object are deleted by your application, the value of the RecordCount property decreases. However, in a multiuser environment, records deleted by other users aren't reflected in the value of the RecordCount property until the current record position is a deleted record. At that time, the setting of the RecordCount property decreases by 1. Using the Requery method on a Recordset object, followed by the MoveLast method, sets the RecordCount property to the current total number of records in the Recordset object.

A snapshot-type or forward-only-type Recordset object is static and its RecordCount property value doesn't change when other users add or delete records in the underlying table.

Finding the Current Position in a Recordset

In some situations, you need to determine how far through a Recordset object you have moved the current record position, and perhaps indicate the current record position to a user. For example, you may want to indicate the current position on a dial, meter, or similar type of control. Two properties are available to indicate the current position: the AbsolutePosition property and the PercentPosition property.

The AbsolutePosition property value is the position of the current record from 0 to one less than the total number of records in a non-table-type Recordset object. However, don't think of this property as a record number; if the current record is undefined, the AbsolutePosition property returns – 1. Further, there is no guarantee that records will appear in the same order every time the Recordset object is accessed.

The PercentPosition property shows the current position expressed as a percentage of the total number of records indicated by the RecordCount property. Because the RecordCount property does not reflect the total number of records in the Recordset object until the Recordset object has been fully populated, the PercentPosition property reflects the current record position only as a percentage of the number of records that have been accessed since the Recordset object was opened. To make sure that the PercentPosition property reflects the current record position relative to the entire Recordset object, use the MoveLast and MoveFirst methods immediately after opening the Recordset object. This fully populates the Recordset object before you attempt to use the PercentPosition property. If you have a large result set, you may not want to use the MoveLast method because it may take a long time.

Caution The PercentPosition property is only an approximation and shouldn't be used as a critical parameter. This property is best suited for driving an indicator, such as a "percent complete" control, or a similar indicator that marks a user's progress while moving though a set of records.

The following example opens a Recordset object on a table called Employees and fully populates it. The program then lists the content of each field of each record in the table, numbering the records as they are printed. When half the records have been printed, a message indicates that the user is more than halfway through the table. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
With rst
 ' Move to end of recordset.
 .MoveLast
 ' Move back to start of recordset.
 .MoveFirst
 Do While Not .EOF
  Debug.Print "Record No. " & .AbsolutePosition + 1
  Debug.Print !FirstName & " " & !LastName
  Debug.Print "AbsolutePosition = " & .AbsolutePosition
  Debug.Print "PercentPosition = " & .PercentPosition
  If .PercentPosition > 50 Then
   Debug.Print "More than half way!"
  End If
  Debug.Print
  .MoveNext
 Loop
 .Close
End With

Finding a Specific Record

The previous section, "Moving Through a Recordset," explores ways you can use the Move methods -- MoveFirst, MoveLast, MovePrevious, and MoveNext -- to loop through a Recordset object. In most cases, however, it's more efficient to search for a specific record.

For example, you may want to find a particular employee based on an employee number, or you may want to find all the order detail records that belong to a specific order. In these cases, looping through all the employee or order detail records could be time consuming. Instead, you can use the Seek method to locate records with table-type Recordset objects, and the Find methods with dynaset-, snapshot-, forward-only-, and dynamic-type Recordset objects.

Finding a Record in a Table-Type Recordset Object

You use the Seek method to locate a record in a table-type Recordset object. To locate a record in a non-table-type Recordset object, use one of the Find methods described in the next section. When you use the Seek method to locate a record, Microsoft Jet uses the table's current index, as defined by the Index property.

The syntax for the Seek method is:

recordset.Seek comparison, key1, key2 ... key13

The table argument is the table-type Recordset object you're searching. The Seek method accepts a number of arguments, the first of which is comparison, a string that determines the kind of comparison being performed. The following table lists the comparison strings you can use with the Seek method.

The key arguments are a series of one or more values (up to 13) that correspond to the field or fields that make up the current index of the Recordset object. Microsoft Jet compares these values to values in the corresponding fields of the Recordset object's records.

The following example opens a table-type Recordset object on the Suppliers table, and uses the Seek method to locate the first record whose SupplierID field matches the value of the intID variable. It then changes the value of the CompanyName field to the value of the strNewName variable and saves the change with the Update method. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Suppliers", dbOpenTable)
With rst
 ' Define current index.
 .Index = "PrimaryKey"
 ' Seek record.
 .Seek "=", intID
 ' If SupplierID is found, change company name.
 If Not .NoMatch Then
  .Edit
  !CompanyName = strNewName
  .Update
 Else
  MsgBox "No record found for SupplierID: " & intID
 End If
 .Close
End With

If you use the Seek method on a table-type Recordset object without first setting the current index, a run-time error occurs.

The next example illustrates how you can create a function that uses the Seek method to locate a record by using a multiple-field index. In this example, strDbPath is the path to the NorthwindTables database, lngOrderID is the value of a record's OrderID field, and lngProductID is the value of a record's ProductID field:

Function GetPrice(strDbPath As String, lngOrderID As Long, _
  lngProductID As Long) As Variant
 Dim dbs As Database, rst As Recordset
 Set dbs = OpenDatabase(strDbPath)
 ' Open recordset on table.
 Set rst = dbs.OpenRecordset("Order Details", dbOpenTable)
 ' Specify index for seek.
 rst.Index = "PrimaryKey"
 ' Seek values for both fields in index.
 rst.Seek "=", lngOrderID, lngProductID
 ' Check for matching record.
 If rst.NoMatch Then
  GetPrice = Null
  MsgBox "Couldn't find order detail record."
 Else
  GetPrice = rst!UnitPrice
 End If
 rst.Close
 dbs.Close
 Set dbs = Nothing
End Function

In this example, the table's primary key consists of two fields, OrderID and ProductID. When you call the GetPrice function with a valid (existing) combination of OrderID and ProductID field values, the function returns the unit price from the found record. If the combination of field values you want can't be found in the table, the function returns a Null value.

If the current index is a multiple-field index, trailing key values can be omitted and are treated as Null values. That is, you can leave off any number of key values from the end of a Seek method's key argument, but not from the beginning or the middle. However, if you don't specify all values in the index, you can use only the ">" or "<" comparison operator with the Seek method.

Finding a Record in a Dynaset- or Snapshot-Type Recordset Object

You can use the following methods to locate a record in a non-table-type Recordset object. (To locate a record in a table-type Recordset object, use the Seek method, which is described in the previous section.) Microsoft Jet supports four Find methods:

• The FindFirst method finds the first record satisfying the specified criteria.
• The FindLast method finds the last record satisfying the specified criteria.
• The FindNext method finds the next record satisfying the specified criteria.
• The FindPrevious method finds the previous record satisfying the specified criteria.

When you use the Find methods, you specify the search criteria, which is typically an expression equating a field name with a specific value. If no record matching the specified criteria is found, the Recordset object's NoMatch property is set to True.

The following example looks up phone numbers in an Employees table. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Dim strName As String, strCriteria As String
Dim strSQL As String, strInput As String
Dim strMsg As String
Set dbs = OpenDatabase(strDbPath)
strSQL = "SELECT FirstName, LastName, HomePhone " _
 & "FROM Employees ORDER BY LastName"
' Open snapshot-type recordset.
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
strInput = "Enter the first few letters of the last " _
 & "name of the employee you want to find: "
' Prompt user for input.
strName = InputBox(strInput)
' Check for input.
If Len(strName) = 0 Then Exit Sub
' Append asterisk to user's input.
strCriteria = "LastName Like """ & strName & "*"""
With rst
 ' Find first instance of criteria.
 .FindFirst strCriteria
 Do
  ' Check for match.
  If Not .NoMatch Then
   strMsg = "The number for " & !FirstName & _
    " " & !LastName & " is " & _
    !HomePhone & ". Search again?"
   ' Display result and prompt user to search again.
   If MsgBox(strMsg, vbYesNo) = vbYes Then
    strName = InputBox(strInput)
    If Len(strName) = 0 Then Exit Sub
    strCriteria = "LastName Like """ & strName & "*"""
    .FindFirst strCriteria
   Else
    Exit Sub
   End If
  Else
   strMsg = "There was no match found for " _
    & " an employee last name like '" & _
    strName & "*'"
   MsgBox strMsg, vbOKOnly, "Find Home Phone"
   Exit Sub
  End If
 Loop
 .Close
End With
dbs.Close
Set dbs = Nothing

You can locate the matching records in reverse order by finding the last occurrence with the FindLast method and then using the FindPrevious method instead of the FindNext method.

Microsoft Jet sets the NoMatch property to True whenever a Find method fails and the current record position is undefined. There may be a current record, but you have no way to tell which one. If you want to be able to return to the previous current record following a failed Find method, use a bookmark (as described in the following section).

The NoMatch property is False whenever the operation succeeds. In this case, the current record position is the record found by one of the Find methods.

The following example illustrates how you can use the FindNext method to find all records in the Customers table that contain a Null value in the Fax field. It then prompts the user to enter a fax number for the customer. In this example, strDbPath is the path to the database:

Dim dbs As Database, rst As Recordset
Dim strCriteria As String, varNewFax As Variant
Dim strMsg As String
 
Set dbs = OpenDatabase(strDbPath)
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset)
' Check for Null value in Fax field.
strCriteria = "Fax Is Null"
With rst
 ' Find first Null value.
 .FindFirst strCriteria
 Do Until .NoMatch
  ' Prompt user to enter fax number.
  varNewFax = InputBox(!CompanyName & " is missing " & _
   "a fax number. Please enter the fax number now.")
  If Len(varNewFax) = 0 Then
   strMsg = "Do you want to find " _
    & "the next missing fax number?"
   If MsgBox(strMsg, vbYesNo, "Continue Search?") = vbNo Then
    Exit Sub
   Else
    varNewFax = Null
   End If
  End If
  ' Edit and update record.
  .Edit
  !Fax = varNewFax
  .Update
  ' Find next Null value.
  .FindNext strCriteria
 Loop
 .Close
End With

If you need to frequently search a large number of records in a dynaset, you may get better performance by creating a temporary indexed table and using the Seek method instead.

Marking Record Positions with Bookmarks

A bookmark is a system-generated Byte array that uniquely identifies each record. The Bookmark property of a Recordset object changes each time you move to a new record. To identify a record, move to that record and then assign the value of the Bookmark property to a variable of type Variant. To return to the record, set the Bookmark property to the value of the variable.

The following example illustrates how you can use a bookmark to save the current record position and then quickly return to that record position if a Find or Seek method fails. Using a bookmark in this way is useful, because if the method fails, the current record position is undefined. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Dim varOrigin As Variant
Set dbs = OpenDatabase(strDbPath)
' Open table-type recordset.
Set rst = dbs.OpenRecordset("Customers", dbOpenTable)
With rst
 ' Set recordset index.
 .Index = "CompanyName"
 MsgBox "Current Record: CompanyName = " & !CompanyName
 ' Return bookmark.
 varOrigin = .Bookmark
 ' Perform seek.
 .Seek ">=", "Z"
 ' Check for match.
 If .NoMatch Then
  MsgBox "Can't find a company name starting with 'Z'. ", _
   vbOKOnly, "There is no current record!"
  ' Set bookmark.
  .Bookmark = varOrigin
 End If
 MsgBox "Current Record: CompanyName = " & !CompanyName
 .Close
End With

In this example, the Bookmark property for the current record is saved, and if the Seek method fails, the Bookmark property is reset to its previous value.

The LastModified property of the Recordset object provides a good illustration of how to use a bookmark. The LastModified property is the bookmark of the last record in the Recordset object to be added or modified. To use it, set the Recordset object's Bookmark property equal to the LastModified property as follows:

rst.Bookmark = rst.LastModified

This moves the current record position to the last record that was added or modified. This is particularly useful when you are adding new records, because by default, the current record position after you add a new record is the record you were on before using the AddNew method. With the LastModified property, you can easily make the newly added record the current record if that's what your application expects.

Bookmark Scope

When you close a Recordset object, any bookmarks you saved become invalid. You can't use a bookmark from one Recordset object in another Recordset object, even if both Recordset objects are based on the same underlying table or query. However, you can use a bookmark on the duplicate (clone) of a Recordset object, as shown in the following example. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rstOrig As Recordset
Dim rstClone As Recordset, varPlace As Variant
Set dbs = OpenDatabase(strDbPath)
Set rstOrig = dbs.OpenRecordset("Customers", dbOpenDynaset)
rstOrig.MoveLast
' Save current record location.
varPlace = rstOrig.Bookmark
' Create duplicate recordset.
Set rstClone = rstOrig.Clone
MsgBox "Before setting bookmark: Current Record CompanyName = " _
 & rstClone!CompanyName
' Go to saved record.
rstClone.Bookmark = varPlace
MsgBox "After setting bookmark: Current Record CompanyName = " _
 & rstClone!CompanyName

Microsoft Access Users You can also use the Bookmark property on the Recordset object underlying a form. With this property, your code can mark which record is currently displayed on the form, and then change the record being displayed. For example, on a form containing employee information, you may want a button that a user can click to show the record for an employee's supervisor. The following example illustrates the event procedure you would use for the button's Click event:

Private Sub cmdShowSupervisor_Click()
    Dim rst As Recordset
    Dim varOrigin As Variant, strEmployee As String
    Dim strSuper As String
    ' Open Recordset object.
    Set rst = Me.RecordsetClone
    
    ' Store bookmark for current record.
    varOrigin = Me.Bookmark
    ' Store first and last name of employee.
    strEmployee = Me!FirstName & " " & Me!LastName
    ' Check whether employee reports to anyone.
    If Not IsNull(Me!ReportsTo) Then
        ' Search for first record matching value of ReportsTo field.
        rst.FindFirst "EmployeeID = " & Me!ReportsTo
        ' Check whether record for supervisor exists in table.
        If rst.NoMatch Then
            MsgBox "Couldn't find " & strEmployee & "'s supervisor."
        Else
            ' If supervisor record exists, set form's bookmark.
            ' Current record is now supervisor's record.
            Me.Bookmark = rst.Bookmark
            ' Store supervisor's first and last name.
            strSuper = Me!FirstName & " " & Me!LastName
            ' Display supervisor's name.
            MsgBox strEmployee & "'s supervisor is " & _
                strSuper & "."
            ' Move back to employee's record.
            Me.Bookmark = varOrigin
        End If
    Else
        MsgBox strEmployee & " has no supervisor."
    End If
    rst.Close
End Sub

Why Use Bookmarks Instead of Record Numbers?

If you have used another database or programming environment, you may be accustomed to referring to record numbers. For example, you may have written code that opens a text file and thereafter refers to specific records by their relative position in the file. The first record in the file would be record 1, the second would be record 2, and so on.

In Microsoft Jet databases, your view of records (a Recordset object) is usually a subset of the records in one or more tables. Because the actual number of records in a Recordset object can change at any time, especially in a multiuser environment, there's no absolute record number you can always use to refer to a particular record. The AbsolutePosition property is not the same as a record number, because this property changes if a lower-numbered record is deleted.

Furthermore, records returned in a Recordset object appear in no particular order, unless the Recordset object was created with a query that includes an ORDER BY clause, or is a table-type Recordset object with an index. For this reason, record numbers are meaningless in a Recordset object.

Instead of record numbers, Microsoft Jet provides bookmarks so that you can uniquely identify a particular record. A given record retains its unique bookmark for the life of the Recordset object.

Visual C++ Users Bookmarks are especially useful in conjunction with the GetRowsEx method in the DAO Software Development Kit (SDK). If you choose dbBindBookmark as one of the fields to be bound, it will retrieve a bookmark as a pseudo-field. This enables you to combine the speed and convenience of the GetRowsEx method with easy updating.

For example, you could have retrieved code to fill a list box that looks something like:

// Structure for DoGetRowsEx
typedef struct
 {
 LONG  lEmpId;
 LPVOID  lpbm;
 TCHAR  *lpstrLastName;
 TCHAR  strFirstName[20];
 } EMP, *LPEMP ;
// Employee table binding
DAORSETBINDING Bindings[] = 
{
//Index Type   Column   Type    Offset       Size
{dbBindIndexINT, EMP_ID,  dbBindI4,  offsetof(EMP,lEmpId),  sizeof(LONG)},
{dbBindIndexINT, NULL,  dbBindBookmark, offsetof(EMP,lpbm),   sizeof(LPVOID)},
{dbBindIndexINT, EMP_LNAME, dbBindLPSTRING, offsetof(EMP,lpstrLastName),sizeof(TCHAR *)},
{dbBindIndexINT, EMP_FNAME, dbBindSTRING, offsetof(EMP,strFirstName), sizeof(TCHAR) * 20}
};
// Perform C++ GetRowsEx against the Employee table.
void CDlg::DoGetRowsEx() 
{
 LPEMP   pEmpRows = new EMP[MAX_EMP_REC];
 CString   strLBRow;
 TCHAR   szId[16];
 LONG   lNumRecords;
 LONG   lCount;
 LONG   cbBuf = ( MAX_EMP_REC * sizeof(TCHAR) * 15 ); // Allow average of 15 chars/name.
 LPVOID   pvBuf = new LPVOID[cbBuf]; 
 //Perform GetRows on Employee table.
 //This GetRows uses a specific C++ structure.
 lNumRecords = m_rs.GetRowsEx(pEmpRows, sizeof(EMP),
         &Bindings[0], sizeof(Bindings) / sizeof(DAORSETBINDING),
         pvBuf, cbBuf,
         MAX_EMP_REC); //Arbitrarily get MAX_EMP_REC rows.
 
//Step through the returned rows. 
 for (lCount = 0; lCount < lNumRecords; lCount++)
  {
  strLBRow.Empty();
  wsprintf(szId, _T("%d,  "), pEmpRows[lCount].lEmpId);
  strLBRow += szId;
  strLBRow += pEmpRows[lCount].lpstrLastName;
  strLBRow += _T(", ");
  strLBRow += (LPCTSTR) pEmpRows[lCount].strFirstName;
  //Put the bookmark in the global array.
  m_rgbm[lCount] = CdbBookmark((LPSAFEARRAY)pEmpRows[lCount].lpbm );
  // Put a pointer to it in the ItemData for the list box entry.
  m_lstEmps.SetItemData( m_lstEmps.AddString(strLBRow),
        (DWORD)&m_rgbm[lCount] );
  }
 delete [] pEmpRows;
 delete [] pvBuf;
}

The prior code example could be combined with selection code that moves the current record as follows:

void CDlg::OnSelchangeListEmployees() 
{
 // Cast the dword held in ItemData back into a pointer to a
 // bookmark. The actual bookmarks are being held in m_rgbm
 // and will automatically deallocate the system safe arrays and
 // destruct on CDlg destruction.
 CdbBookmark *pbm = (CdbBookmark *)m_lstEmps.GetItemData(m_lstEmps.GetCurSel());
 m_rs.SetBookmark( *pbm );
 m_strMoreInfo.Format(
  _T("Notes:  %s\n"), 
  (LPCTSTR)( m_rs[15].GetValue().bstrVal ) );
 UpdateData( FALSE ); 
}

Which Recordset Objects Support Bookmarks?

Dynasets based on certain linked tables, such as Paradox tables that have no primary key, do not support bookmarks, nor do forward-only-type or snapshot-type Recordset objects opened with the dbOpenForwardOnly constant.

You can determine whether a given Recordset object supports bookmarks by checking the value of the Bookmarkable property, as in the following example:

If rstLinkedTable.Bookmarkable Then
 MsgBox "The underlying table supports bookmarks."
Else
 MsgBox "The underlying table doesn't support bookmarks."
End If

If you try to use bookmarks on a Recordset object that doesn't support bookmarks, a run-time error occurs.

Changing Data

After you've created a table- or dynaset-type Recordset object, you can change, delete, or add new records. You can't change, delete, or add records to a forward-only- or snapshot-type Recordset object. This section presents the methods and procedures for changing data in table- and dynaset-type Recordset objects.

Using Parameter Queries

In many situations, you'll want a user or another program to provide parameters to your stored queries and Recordset objects. Microsoft Jet provides the means to do this. You first create a QueryDef object, specifying which parameters are to be provided by the end user. When you open a Recordset object based on the QueryDef object, the application opens a dialog box that prompts the user to enter a value, such as the criteria for a WHERE clause or the field on which to sort the selected records.

The following example creates a temporary query that returns the name and hire date of each employee hired after a certain date. Before running the query, the program calls the InputBox function to prompt the user for a threshold date. The names are then shown in the Debug window, starting with the most recent hire. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim strSQL As String, strInput As String
Set dbs = OpenDatabase(strDbPath)
' Create SQL statement with parameters.
strSQL = "PARAMETERS BeginningDate DateTime; " & _
 "SELECT FirstName, LastName, HireDate FROM Employees " & _
 "WHERE HireDate >= BeginningDate " & _
 "ORDER BY HireDate ASC;"
' Create temporary QueryDef.
Set qdf = dbs.CreateQueryDef("", strSQL)
' Prompt user for input.
strInput = InputBox("Enter the earliest hire date:")
If Len(strInput) = 0 Then Exit Sub
If Not IsDate(strInput) Then Exit Sub
' Set parameter value.
qdf.Parameters("BeginningDate") = strInput
' Open recordset.
Set rst = qdf.OpenRecordset()
With rst
 If .BOF Then
  MsgBox "No employees hired after date: " & strInput
  Exit Sub
 End If
 Do While Not .EOF
  Debug.Print !FirstName & " " & !LastName & " was hired on: " & !HireDate
  .MoveNext
 Loop
 .Close
End With

Most of the database maintenance tasks described in the rest of this chapter can be accomplished by using stored parameter queries.

Making Bulk Changes

Many of the changes you may otherwise perform by looping through all the records in a Recordset object can be done more efficiently with an update or delete query. For example, the following code shows how to update employee records to change the Title field value for all sales representatives to "Account Executive". In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, qdf As QueryDef
Set dbs = OpenDatabase(strDbPath)
' Create temporary QueryDef object.
Set qdf = dbs.CreateQueryDef("")
With qdf
 .SQL = "UPDATE Employees " & _
  "SET Employees.Title = ""Account Executive"" " & _
  "WHERE Employees.Title = ""Sales Representative"";"
 ' Invoke query.
 .Execute dbFailOnError
End With

Of course, the entire SQL string in this example can be replaced with a stored or temporary parameter query, in which case the program would prompt the user for parameter values. The following example shows how the previous example might be rewritten as a parameter query. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, qdf As QueryDef
Dim strSQLUpdate As String, strOld As String, strNew As String
Set dbs = OpenDatabase(strDbPath)
strSQLUpdate = "PARAMETERS [Old Title] Text, [New Title] Text; " & _
 "UPDATE Employees " & _
 "SET Employees.Title = [New Title] " & _
 "WHERE Employees.Title = [Old Title]; "
' Create the QueryDef object.
Set qdf = dbs.CreateQueryDef("", strSQLUpdate)
' Prompt for old title.
strOld = InputBox("Enter old job title")
If Len(strOld) = 0 Then Exit Sub
' Prompt for new title.
strNew = InputBox("Enter new job title")
If Len(strNew) = 0 Then Exit Sub
With qdf
 ' Set parameter values.
 .Parameters("Old Title") = strOld
 .Parameters("New Title") = strNew
 ' Invoke query.
 .Execute
End With

Modifying an Existing Record

Changing an existing record in a Recordset object is a four-step process:

1. Go to the record you want to change.

2. Use the Edit method to prepare the current record for editing.

3. Make the necessary changes to the record.

4. Use the Update method to save the changes to the current record.

The following example illustrates how to change the job titles for all sales representatives in a table called Employees. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset, strCriteria As String
Set dbs = OpenDatabase(strDbPath)
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
strCriteria = "Title = ""Sales Representative"""
With rst
 ' Move to start of recordset.
 .MoveFirst
 ' Find first matching record.
 .FindFirst strCriteria
 Do While Not .NoMatch
  ' Edit record and update.
  .Edit
  !Title = "Account Executive"
  .Update
  ' Find next matching record.
  .FindNext strCriteria
 Loop
End With

If you don't use the Edit method before you try to change a value in the current record, a run-time error occurs.

You can also terminate the Edit method and any pending transactions without saving changes by using the CancelUpdate method. While you can terminate the Edit method just by moving off the current record, this is not practical when the current record is the first or last record in the Recordset object, or is a new record. It's therefore generally simpler to use the CancelUpdate method.

Inconsistent Updates

Dynaset-type Recordset objects can be based on a multiple-table query, with the query often implementing a one-to-many relationship. For example, suppose you want to create a multiple-table query that combines fields from the Orders and Order Details tables. Generally speaking, you can't change values in the Orders table because it's on the "one" side of the relationship. Depending on your application, however, you may want to be able to make changes to the Orders table. To make it possible to freely change the values on the "one" side of a one-to-many relationship, use the dbInconsistent constant of the OpenRecordset method to create an inconsistent dynaset.

Set rst = dbs.OpenRecordset("Sales Totals",, dbInconsistent)

When you update an inconsistent dynaset, you can easily destroy the relational integrity of the data in the dynaset. You must take care to understand how the data is related across the one-to-many relationship and update the values on both sides in a way that preserves data integrity.

The dbInconsistent constant is available only for dynaset-type Recordset objects. It's ignored for table-, snapshot-, and forward-only-types, but no compile or run-time error is returned if dbInconsistent is used with those types of Recordset objects.

Even with an inconsistent Recordset object, some fields may not be updatable. For example, you can't change the value of an AutoNumber field, and a Recordset object based on certain linked tables may not be updatable.

Deleting an Existing Record

You can delete an existing record in a table- or dynaset-type Recordset object by using the Delete method. You can't delete records from a snapshot- or forward-only-type Recordset object. The following example deletes all orders prior to 10-1-94 from the Orders table in the NorthwindTables database. In this example, strDbPath is the path to the database:

Dim dbs As Database, rst As Recordset
Dim strCriteria As String
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
strCriteria = "OrderDate < #10-1-94#"
With rst
 .FindFirst strCriteria
 Do While Not .NoMatch
  .Delete
  .FindNext strCriteria
 Loop
 .Close
End With

When you use the Delete method, Microsoft Jet immediately deletes the current record without any warning or prompting. Deleting a record doesn't automatically cause the next record to become the current record; to move to the next record, you must use the MoveNext method. Keep in mind, however, that after you've moved off the deleted record, you cannot move back to it.

If you try to access a record after deleting it from a table-type Recordset object, you'll get error 3167, "Record is deleted." On a dynaset you'll get error 3021, "No current record."

If you have a Recordset object clone positioned at the deleted record and try to read its value, you'll get error 3167 regardless of the type of Recordset object. Trying to use a bookmark to move to a deleted record will also result in error 3167.

Adding a New Record

Adding a new record to a dynaset- or table-type Recordset object is a three-step process:

1. Use the AddNew method to prepare a new record for editing.

2. Assign values to each of the record's fields.

3. Use the Update method to save the new record.

The following example adds a new record to a table called Shippers. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Shippers")
With rst
 ' Add new record.
 .AddNew
 ' Supply values for fields.
 !CompanyName = "Global Parcel Service"
 !Phone = "(503)555-9786"
 ' Update.
 .Update
 .Close
End With

When you use the AddNew method, Microsoft Jet prepares a new, blank record and makes it the current record. When you use the Update method to save the new record, the record that was current before you used the AddNew method becomes the current record again. The new record's position in the Recordset object depends on whether you added the record to a dynaset- or a table-type Recordset object.

If you add a record to a dynaset-type Recordset object, the new record appears at the end of the Recordset object, no matter how the Recordset object is sorted. To force the new record to appear in its properly sorted position, you can use the Requery method or re-create the Recordset object.

If you add a record to a table-type Recordset object, the record appears positioned according to the current index, or at the end of the table if there is no primary key and no current index. Because Microsoft Jet version 3.5 allows multiple users to create new records on a table simultaneously, your record may not appear at the end of the Recordset object as it did in previous versions of Microsoft Jet. Be sure to use the LastModified property rather than the MoveLast method to move to the record you just added.

Caching ODBC Data with a Recordset

You can use a dynaset-type Recordset object to create a local cache for ODBC data. Creating a local cache lets you retrieve records in batches instead of one at a time as each record is requested, and makes much better use of your server connection, improving performance.

The CacheSize and CacheStart properties establish the size and starting offset (expressed as a Bookmark property value) for the cache. For example, you may set the CacheSize property to 100 records. Then, by using the FillCache method, you can retrieve sufficient records to fill the cache.

Tracking Recordset Changes

You may need to determine when the design of the underlying table of a table-type Recordset object was created, or the last time it was modified. The DateCreated and LastUpdated properties, respectively, give you this information. Both properties return the date stamp applied to the table by the machine on which the table resided at the time it was stamped.

Microsoft Jet Transactions

Defined as a "logical unit of work," a transaction is one of the features common to most database management systems. By wrapping multiple database operations into a single unit, transactions offer the developer the ability to enforce data integrity by making sure multiple operations can be treated by the engine as an "all or nothing" proposition, thereby never allowing the database to end up in an inconsistent state.

The most common example of transaction processing involves a bank's automated teller machine. The processes of dispensing cash and then debiting the user's account are considered a logical unit of work and are wrapped in a transaction: The cash is not dispensed unless the system is also able to debit the account. By using a transaction, the entire operation either succeeds or fails. This maintains the consistent state of the ATM database.

Transactions can be defined by what are known as the ACID properties. The following attributes of transactions make up the ACID acronym:

• Atomic denotes that transactions are all-or-nothing operations. Each operation wrapped in a transaction must be successful for all operations to be committed.
• Consistent denotes that a transaction enables data operations to transform the database from one consistent state to another, even though at any point during the transaction the database may be inconsistent.
• Isolated denotes that all transactions are "invisible" to other transactions. That is, no transaction can see another transaction's updates to the database until the transaction is committed.
• Durable denotes that after a transaction commits, its updates survive -- even if there is a subsequent system crash.

With file-server databases such as Microsoft Jet, the concept of durability becomes somewhat complicated. There are currently no file-server based database engines that can fully support this criterion of true transactions. For example, a database connected to a file server cannot be expected to fully support the durability rule if the file-server crashes before a transaction has had time to commit its changes. If you require true transaction support with respect to durability, you should investigate the use of a client/server architecture.

Using Transactions in Your Applications

Microsoft Jet supports transactions through the BeginTrans, CommitTrans and Rollback methods of the Workspace object. The basic syntax is shown in the following table.

The following example changes the job title of all sales associates in the Employees table of the NorthwindTables database. After the BeginTrans method starts a transaction that isolates all of the changes made to the Employees table, the CommitTrans method saves the changes. Note that you can use the Rollback method to undo changes that you saved with the Update method. In this example, strDbPath is the path to the database:

Dim dbs As Database
Dim rst As Recordset
Dim wsp As Workspace
Dim strCriteria As String
 
' Return reference to default workspace.
Set wsp = DBEngine.Workspaces(0)
Set dbs = OpenDatabase(strDbPath)
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
strCriteria = "Title = ""Sales Associate"""
' Begin transaction.
wsp.BeginTrans
With rst
 ' Search recordset for records matching criteria.
 .MoveFirst
 .FindFirst strCriteria
 ' Edit matching records.
 Do While Not .NoMatch
  .Edit
  !Title = "Sales Representative"
  .Update
  .FindNext strCriteria
 Loop
End With
' Prompt user to save changes or roll back.
If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
 wsp.CommitTrans
Else
 wsp.Rollback
End If
rst.Close
dbs.Close

Managing Transactions

Microsoft Jet uses sophisticated algorithms to enhance transaction performance, reliability, and usability. This section discusses topics related to how the engine manages transactions.

The Temporary Database

Almost all database-transaction systems store intermediate changes in a temporary log file instead of writing them directly to the database. Microsoft Jet uses a similar mechanism in that it buffers all transaction activity to a temporary database. When the transaction is committed, the contents of the temporary database are merged into the real database. If you issue a Rollback method to cancel the transaction, the engine frees the pages in the temporary database.

Microsoft Jet does not create the temporary database until it has to. It uses whatever cache memory is available to store changes to data. After the cache is exhausted, the engine creates the temporary database and starts to write changes there.

Microsoft Jet creates the temporary database in the directory specified by the TEMP environment variable of the workstation. If the available disk space for the temporary database is exhausted during a transaction, a trappable run-time error occurs. If you attempt to commit the transaction after this error occurs, the engine will commit an indeterminate number of changes, possibly leaving the database in an inconsistent state. To ensure a consistent database state, you usually should roll back the transaction when this error occurs.

Although the temporary database is a Microsoft Jet database, it's used internally by the engine only. It cannot be opened from other applications. After a transaction is complete, the engine frees the pages in the temporary database.

Nesting Transactions

In Microsoft Jet workspaces, you can have up to five levels of transactions active at any one time by nesting combinations of the BeginTrans and CommitTrans methods or the Rollback method. If you nest transactions, you must make sure you commit or roll back the current transaction before trying to commit or roll back a transaction at a higher level of nesting.

If you want to have additional levels of nesting, or want to have transactions with overlapping, non-nested scopes, you can open additional Workspace objects and manage other transactions within those new workspaces.

When a Transaction Is Rolled Back by the Engine

If you close a database variable, any uncommitted transactions that were within the scope of that database variable are automatically rolled back. Microsoft Jet never automatically commits any transactions you have started. This behavior is also true of the Workspace object. If you close a Workspace object, any transactions within the scope of the workspace are rolled back. You should be aware of this behavior when you write your code. Never assume that the engine is going to commit your transaction for you.

Transactions on External Data Sources

Transactions are not supported on external non-Microsoft Jet data sources, with the exception of ODBC data. For example, if your database has linked FoxPro or dBASE tables, any transactions on those objects are ignored. This means that the transaction will not fail or generate a run-time error, but it won't actually do anything, either.

You may recall from Chapter 1, "An Overview of Microsoft Jet," that Microsoft Jet 2.x databases are opened by Microsoft Jet 3.5 as external ISAM databases. However, unlike the other non-native external data sources, the engine does support transactions on Microsoft Jet 2.x databases.

You can check the value of the Transactions property on Database and Recordset objects to determine whether the object supports transactions. A value of True indicates that the object does support transactions, and a value of False indicates that the object does not support transactions. In an ODBCDirect workspace, the Transactions property is available on Connection and Database objects, and indicates whether the ODBC driver you are using supports transactions.

Transactions and Performance

In versions 1.x and 2.x of Microsoft Jet, it was generally recommended that you use transactions as a performance enhancement. In almost every case, wrapping DAO updates in a transaction is much faster in versions 1.x and 2.x because the transaction forces the engine to buffer changes in the cache rather than writing them directly to disk. However, various performance enhancements have been made in Microsoft Jet 3.0 and 3.5 to remove the need to use transactions for performance reasons.

Microsoft Jet 3.0 and 3.5 provide automatic, internal transactions for all DAO add, update, and delete operations. In most situations, this automatic support provides your application with the best possible performance. However, there may be situations where you want to fine-tune the engine's internal transaction behavior. You can do this by creating and modifying various settings in the Windows registry.

The default settings used by Microsoft Jet for controlling transactions are generally the best available for general-purpose application design. However, you may find that tuning these settings yourself may enable the engine to better handle your data.

The Engine's Two Modes

Microsoft Jet has two modes of operation when it comes to transaction processing: synchronous and asynchronous. Synchronous processing was the only mode available in version 2.x of the engine.

Synchronous processing allows nothing else to occur until the current operation is completed. This is the model that Microsoft Jet 2.x follows.

Asynchronous processing involves the engine queuing up a series of changes to the database. For example, several recordset Update methods could be grouped together. At some point, these updates are written to disk and the engine begins grouping new changes. The updates occur when one the following happens:

• A specified period of time passes after the first stored change (controlled by either the FlushTransactionTimeout or the ExclusiveAsyncDelay and SharedAsyncDelay registry settings).
• The internal memory set aside for updates (as defined by the MaxBufferSize registry setting) is exceeded.

To better understand these modes, consider the following DAO code:

Set rst = dbs.OpenRecordset("SELECT * FROM Customers")
While Not rst.EOF
  rst.Edit
   '......
   rst.Update
   rst.MoveNext
Wend

With synchronous processing, Microsoft Jet would not continue the loop until rst.Update completed successfully.

With asynchronous processing, Microsoft Jet would immediately process rst.Update, and while it was using a thread to do the work, would continue on another thread to process rst.MoveNext.

Microsoft Jet places temporary files on disk according to the paths specified by the TEMP environment variable. The behavior is the same for Windows 95 and Windows NT Workstation or Windows NT Server.

Extracting Data from a Record

After you've located a particular record or records, you may want to extract data to use in your application instead of modifying the underlying source table.

A single field of a record can be copied to a variable of the appropriate data type. The following example extracts data from three fields in the first record in a Recordset object. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset
Dim strFirstName As String, strLastName As String, strTitle As String
 
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Employees")
With rst
 .MoveFirst
 strFirstName =!FirstName
 strLastName =!LastName
 strTitle =!Title
End With

Copying Entire Records to an Array

To copy one or more entire records, you can create a two-dimensional array and copy records one at a time. You increment the first subscript for each field and the second subscript for each record.

A fast way to do this is with the GetRows method. The GetRows method returns a two-dimensional array. The first subscript (an index into the array) identifies the field, and the second identifies the record number, as follows:

varRecords(intField, intRecord)

The following example uses an SQL statement to retrieve three fields from a table called Employees and place them in a Recordset object. It then uses the GetRows method to retrieve the first three records of the Recordset object, and stores the selected records in a two-dimensional array. Each record is then printed, one field at a time, by using the two array indexes to select specific fields and records.

To clearly illustrate how the array indexes are used, the following example uses a separate statement to identify and print each field of each record. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database
Dim rst As Recordset
Dim varRecords As Variant
Dim intNumReturned As Integer, intNumFields As Integer
Dim intField As Integer, intRecord As Integer
Dim strSQL As String
Set dbs = OpenDatabase(strDbPath)
strSQL = "SELECT FirstName, " & _
 "LastName, Title FROM Employees"
' Open snapshot-type recordset.
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
' Return array using GetRows.
varRecords = rst.GetRows(3)
' Determine upper bound of second dimension.
intNumReturned = UBound(varRecords, 2)
' Determine upper bound of first dimension.
intNumFields = UBound(varRecords, 1)
' Print each value in array.
For intRecord = 0 To intNumReturned
 For intField = 0 To intNumFields
  Debug.Print varRecords(intField, intRecord)
 Next intField
 Debug.Print
Next intRecord

You can use subsequent calls to the GetRows method if more records are available. Because the array is filled as soon as you call the GetRows method, this approach is much faster than using assignment statements to copy one field at a time.

Note also that you don't have to dimension the Variant variable as an array, because this is done automatically when the GetRows method returns records. This enables you to use fixed-length array dimensions without knowing how many records or fields will be returned, instead of using variable-length dimensions that take up more memory.

If you're trying to retrieve all the records by using multiple calls to the GetRows method, use the EOF property to be sure that you're at the end of the Recordset object. The GetRows method returns fewer than the number requested if it's at the end of the Recordset object or if it cannot retrieve a record in the range requested. For example, if the fifth record cannot be retrieved in a group of ten records that you're trying to retrieve, the GetRows method returns four records and leaves the current record position on the record that caused a problem -- and does not generate a run-time error. This situation may occur if a record in a dynaset was deleted by another user. If fewer records were returned than the number requested and you're not at the end-of-file position, you will need to read each field in the current record to determine what error the GetRows method encountered.

Because the GetRows method always returns all the fields in the Recordset object, you may want to create a query that returns just the fields you need. This is especially important for Memo and Long Binary (OLE Object) fields.

Microsoft Excel Users You can import the contents of a Recordset object into a range on a Microsoft Excel worksheet with the CopyFromRecordset method. This method begins to copy at the current record of the result set; when the transfer is completed, the Recordset object pointer is positioned just past the last record, or at the end-of-file position. For more information, see the Microsoft Excel Visual Basic Help file.

Using Field Objects

The default collection of a Recordset object is its Fields collection. This collection includes a single Field object corresponding to each field (or column) in the Recordset object. Each Field object has a set of properties uniquely identifying the field's name, data type, and so on, as well as the value of the field in the current record.

Referencing Field Objects

A Field object can be identified by its Name property, which corresponds to the field name in the table from which the data in the field was retrieved. Because the Fields collection is the default collection of a Recordset object, you can use either of the following syntax forms to return the field's current value:

rst!LastName
rst.Fields("LastName")

Within the Fields collection, each Field object can also be identified by its index:

rst.Fields(0)

The index enables you to walk through the collection in a loop, replacing the index with a variable that is incremented with each pass through the loop. Object numbers in the collection are zero-based, so the first Field in the collection is number 0, the second is 1, and so on. The field order is determined by the underlying table. Fields are usually numbered in the order that they are retrieved when the Recordset object is opened. One drawback to using the index to identify a field is that you can't be certain which field will be referenced, because the underlying table structure may change, fields may be added or deleted, and so on.

The Field object also provides two properties that indicate its position relative to the other fields in the collection -- the OrdinalPosition property and the CollectionIndex property. These properties are closely related, yet subtly different. CollectionIndex is a read-only number that indicates the Field object's absolute position within the collection as determined by the collection population. The OrdinalPosition property, in contrast, is a read-write property that you can set to any positive integer to change the field order when data is displayed in a form or copied to an array or a Microsoft Excel worksheet, and so on. The value of the OrdinalPosition property usually corresponds to the value of the field's CollectionIndex property.

When you write a loop to walk through the Fields collection, the loop counter always identifies fields in the same sequence, which corresponds to the CollectionIndex property, regardless of the OrdinalPosition property's value.

Caution The CollectionIndex property is not exposed to DAO users through the Object Browser. Although CollectionIndex is available, its use is discouraged because at the present time Microsoft is not committed to maintaining it in future versions of DAO. Consequently, this feature is not tested to the same reliability level as other DAO features, and assistance on its use is not available through Microsoft Technical Support.

Microsoft Excel Users You can also copy individual fields from a Recordset object to a ListBox control by using the ListBox control's AddItem method. For more information, see the Microsoft Excel Visual Basic Help file.

Field Data Types

A Field object has a Type property that can be set to one of the following 13 DAO property constants.

For a Field object in a Recordset object, the Type property is read-only. However, you must be aware of the Field object's type when you are copying data to or from a field in code; otherwise, a type mismatch error may occur. For example, you cannot copy text data to a field with the Integer data type.

The Type property of a Field object in a Recordset object is determined by the underlying table from which the record was retrieved. If you created the table and its fields by using DAO DDL statements, you can easily determine the source table's data type. However, if you're retrieving data from tables created with Microsoft Jet SQL DDL statements or through the Microsoft Access user interface, the data type you specified when creating the table may not match one of the 13 DAO data types.

Large Value Fields

Long Binary (OLE Object) and Memo fields are collectively referred to as "large value" fields because they are typically much larger than fields of other data types. A record containing one or more large value fields usually exceeds the 2 KB size limit of a record. When this happens, each large value field is represented in the record by a pointer, which references one or more separate 2 KB memory pages on which the data is actually stored.

When you query tables containing large value fields, don't include those fields in the field list unless you absolutely need them, because returning large value fields takes time.

A snapshot- or forward-only-type Recordset object opened against large value fields in an .mdb file does not actually contain the large value field data. Instead, the Recordset object maintains references to the data in the original tables, the same way a dynaset references all data.

Handling Large Value Data

Sometimes you'll need to read or copy data from a large value field when you don't have sufficient memory to copy the entire field in a single statement. You instead have to break up the data into smaller units, or "chunks," that will fit available memory. The FieldSize method tells you how large the field is, measured in bytes. Then you can use the GetChunk method to copy a specific number of bytes to a buffer, and use the AppendChunk method to copy the buffer to the final location. You then continue using the GetChunk and AppendChunk methods until the entire field is copied.

Reading and Writing Data

When you read and write data to a field, you're actually reading or setting the Value property of a Field object. The Value property is the default property of a Field object, so the following statements are equivalent:

rst!LastName.Value = strName
rst!LastName = strName

Write Permission

Even if a Recordset object is a dynaset or table type object, which are normally updatable, the tables underlying a Recordset object may not permit you to modify data. Check the Updatable property of the Recordset object to determine whether its data can be changed. If the property is True, the Recordset object can be updated.

Individual fields within an updatable Recordset object may not be updatable, so attempting to write to these fields can generate a run-time error. To determine whether a given field is updatable, check the DataUpdatable property of the corresponding Field object in the Fields collection of the Recordset object.

In the following example, a Recordset object variable is passed to a function that first determines whether the Recordset object is updatable. If it is, the function counts how many Field objects are updatable and returns the total. It also prints the Name property of each Field object that is not updatable. If all the fields are updatable, the function returns – 1. If the entire Recordset object is not updatable, the function returns 0. If some of the fields are updatable, the function returns the number of updatable fields.

Function UpdateTest(rst As Recordset) As Integer
 Dim intCount As Integer, intX As Integer
 Dim fld As Field
 ' Initialize counter variable.
 intCount = 0
 ' Check whether recordset is updatable.
 If rst.Updatable Then
  ' Check whether each field is updatable.
  For Each fld In rst.Fields
   If fld.DataUpdatable Then
    ' Increment counter for each updatable field.
    intCount = intCount + 1
   Else
    ' Print nonupdatable fields.
    Debug.Print fld.Name
   End If
  Next fld
  If intCount = rst.Fields.Count Then
   ' If all fields are updatable, return -1.
   UpdateTest = -1
  Else
   ' If some fields are updatable, return number.
   UpdateTest = intCount
  End If
 Else
  ' If no fields are updatable, return 0.
  UpdateTest = 0
 End If
End Function

Constraints

Any single field can impose a number of constraints on data in that field when records are added or updated. These constraints are defined by a handful of properties. The AllowZeroLength property on a Text or Memo field indicates whether the field will accept a zero-length string. The Required property indicates whether some value must be entered in the field, or if it instead can accept a Null value. For a Field object in a Recordset object, these properties are read-only; their state is determined by the underlying table.

Field-Level Data Validation

Validation is the process of determining whether data entered in a field's Value property is within an acceptable range. A Field object in a Recordset object may have the ValidationRule and ValidationText properties set. The ValidationRule property is a criteria expression, similar to the criteria of an SQL WHERE clause, without the WHERE keyword. The ValidationText property is a string that's displayed in an error message by Microsoft Access, or by the Data control in Visual Basic if you attempt to enter data in the field that is outside the limits of the ValidationRule property. If you're coding from DAO directly, you can use the ValidationText property for a message you want to display to the user.