Working with Records and Fields

Article
06/14/2014

CHAPTER 6

Working with Records and Fields

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The Microsoft® Jet database engine supports a rich set of Data Access Objects (DAO) features for organizing, sorting, searching, updating, adding, and deleting data. The Recordset object alone provides 24 methods and 34 properties that give you a great deal of control over records in a database. With the Recordset object's Fields collection and the properties and methods of the Field object, you can manipulate data at the field level. This chapter describes how to manipulate records and fields by using the DAO Recordset and Field objects.

For information about manipulating records and fields using ActiveX® Data Objects (ADO) with the Jet database engine, see Chapter 14, "Working with the Data Access Components of an Office Solution," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

Chapter Contents

Using Recordset Objects

Using Field Objects

Using Recordset Objects

A Recordset object represents the records in a base table or the records that result from running a query. You use Recordset objects to manipulate the data in a database at the record level.

****Note **** You use Field objects to manipulate the data in a database at the field level. For more information, see "Using Field Objects" later in this chapter.

The four types of Recordset objects—table, dynaset, snapshot, and forward-only—differ from each other in significant ways:

A table-type Recordset object can be created from a table in an Access database, but not from an Open Database Connectivity (ODBC) or a linked table. When you create a table-type Recordset, the Jet database engine opens the actual table, and your subsequent data manipulations operate directly on base-table data. A table-type Recordset can be opened on only one table; it cannot be opened on a union query or a select query with a join.

One of the biggest advantages of this type of Recordset object is that you can index it by using an index created for the underlying table. This allows much faster sorting and searching than is possible with the other types. To locate specific records, use the Seek method, which is faster than the Find methods.
A dynaset-type Recordset object can be created from either a local or a linked table, or with a row-returning query that joins tables. 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 updateable joins are a unique feature of dynasets—they enable you to use updateable queries against tables in different types of databases.

One of the main benefits of this type is that a dynaset and its underlying tables update each other. Changes made to records in the dynaset are also made in 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 searches and other manipulations may not run as fast as with a table-type Recordset.
A snapshot-type Recordset object is a static copy of a set of records as it exists at the time the snapshot is created. A snapshot-type Recordset object can contain fields from one or more tables in a database. You can't update a snapshot.

The main advantage of a snapshot is that it creates less processing overhead than the other types, so it can run queries and return data faster, especially when working with ODBC data sources.

****Note **** For .mdb files, OLE Object and Memo fields are represented in a snapshot by pointers, rather than the actual data. For more information about OLE Object and Memo fields, see "The OLE Object and Memo Data Types" later in this chapter.
A forward-only-type Recordset object, sometimes referred to as a forward-scrolling snapshot or a forward-only snapshot, provides a subset of the capabilities of a snapshot. With forward-only snapshots, you can move only in a forward direction through the records. Recordset objects of this type cannot be cloned and only support the Move and MoveNext methods. Like snapshots, you can't update a forward-only-type Recordset object.

The advantage of a forward-only-type Recordset object is that it usually provides the greatest speed. It does, however, offer the least functionality of any Recordset.

****Note **** A snapshot stores a copy of the entire record (except for OLE Object and Memo fields). A dynaset stores just the primary key for each record, copying the full record only when it's needed for editing or display purposes. Since a snapshot stores a complete copy of all the records in a table, a snapshot may perform more slowly than a dynaset if the number of records is large. To determine whether a snapshot or dynaset is faster, you can open the Recordset as a dynaset and then open it as a snapshot to see which provides faster performance.

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. Because table-type Recordset objects are indexed, they also provide the fastest way to locate data. 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 and you only need to scan through a set of records, using a forward-only snapshot may improve performance.

All other things being equal, if a table-type Recordset object is available, using it almost always results in the best performance.

****Note **** In this chapter, the terms table, snapshot, and dynaset are often used for the sake of simplicity. However, keep in mind that these are all types of Recordset objects. For example, the term dynaset refers to a dynaset-type Recordset object, not the obsolete DAO Dynaset object.

Creating a Recordset Object Variable

To create a Recordset object variable, use the OpenRecordset method. First, declare a variable of type Recordset, and then set the variable to the object returned by the OpenRecordset method.

You can use the OpenRecordset method with Database, TableDef, QueryDef, and existing Recordset objects. The syntax of the OpenRecordset method for Database object is:

Setvariable**=database.OpenRecordset(source [, type [, options [, lockedits ]]])**

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

Setvariable**=object.OpenRecordset([type [, options [, lockedits ]]])**

The variable argument is the name of the new Recordset object. The database argument is the name of the open Database object from which you're creating the new Recordset object. The object argument is the TableDef, QueryDef, or existing Recordset object from which you're creating the new Recordset object.

The source argument specifies the source of the records for the new Recordset object. The value of source is the value of the resulting Recordset object's DAO Name property. When you create a new Recordset object from a Database object, the source argument is a TableDef or QueryDef object in the database or a valid row-returning SQL query or statement. When you create a new Recordset object from a TableDef, QueryDef, or existing Recordset object, the object itself provides the data source for the new Recordset.

The type argument is an intrinsic constant that specifies the kind of Recordset object that you want to create. You can use the following constants:

dbOpenTable
dbOpenDynaset
dbOpenSnapshot
dbOpenForwardOnly

****Note **** The dbOpenForwardOnly type constant replaces the dbForwardOnly type constant that was available in previous versions of DAO. You can still use the dbForwardOnly constant, but it's provided only for backward compatibility.

The following sections discuss the type, options, and lockedits arguments in detail.

Default Recordset Types

Because DAO automatically chooses the default Recordset type depending on the data source and how the Recordset is opened, you don't need to specify a Recordset type. However, you can specify a type different from the default by using a type argument in the OpenRecordset method.

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

Using the OpenRecordset method with a Database object:
```
Set rstNew = dbsDatabase.OpenRecordset("Data Source")
```
If Data Source is a table local to the database, all four types are available, and the table-type Recordset object is the default. If Data Source is anything else, only dynaset- and snapshot-type Recordset objects are available, and the dynaset type is the default.
Using the OpenRecordset method with a TableDef****object:
```
Set rstNew = tdfTableData.OpenRecordset
```
If tdfTableData refers to a table in an Access database (.mdb) or to an installable ISAM database opened directly, then all four types are available and the table-type Recordset object is the default type. If tdfTableData is in an ODBC database or is a linked table in an external database, only dynaset- and snapshot-type Recordset objects are available, and the dynaset type is the default.
Using the OpenRecordset method with a QueryDef object:
```
Set rstNew = qdfQueryData.OpenRecordset
```
Only dynaset- and snapshot-type Recordset objects are available, and the dynaset type is the default.
Using the OpenRecordset method with an existing Recordset object:
```
Set rstNew = rstExisting.OpenRecordset
```
Only dynaset- and snapshot-type Recordset objects are available. The default is the type of the existing Recordset, in this case, the type of rstExisting.

OpenRecordset Options

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

dbAppendOnly Users can append new records to the Recordset, but they cannot edit or delete existing records. This is useful in applications that collect and archive data (dynaset only).
dbReadOnly No changes can be made to the Recordset. This argument is provided only for backward compatibility. Use the dbReadOnly constant in the lockedits argument instead.
dbSeeChanges If another user changes data in a record on which this Recordset has invoked the Edit method, but before it has invoked the Update method, a run-time error occurs. This is useful in applications where multiple users have simultaneous read/write permission on the same data (dynaset and table only).
dbDenyWrite When used on a dynaset or snapshot, this option prevents other users from adding or modifying records, although they can still read data. When used on a table, no other user can open any type of Recordset from an underlying table.
dbDenyRead Other users cannot read data in the table (table only).
dbForwardOnly This option creates a forward-only snapshot. It is provided only for backward compatibility. Use the dbOpenForwardOnly constant in the type argument instead.
dbSQLPassThrough When the source argument is an SQL statement, use this constant to pass the SQL statement to an ODBC database for processing. If used with a dynaset, data isn't updateable (dynaset and snapshot only).
dbConsistent (Default) Only consistent updates are allowed (dynaset only). You can't use this constant with the dbInconsistent constant.
dbInconsistent Inconsistent updates are allowed. This is the opposite of dbConsistent (dynaset only). You can't use this constant with the dbConsistent constant.

****See Also **** For more information about inconsistent updates, see "Inconsistent Updates" later in this chapter.

With the lockedits argument of the OpenRecordset method, you can control how locking is handled for a Recordset object. You can use the following constants:

dbReadOnly No changes can be made to the Recordset. This constant replaces the dbReadOnly constant that was used in the options argument in previous versions of DAO.
dbPessimistic (Default) Microsoft Jet uses pessimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record you're editing is locked as soon as you use the Edit method.
dbOptimistic Microsoft Jet uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record is not locked until the Update method is executed.

The default value is dbPessimistic. The only effect of using dbPessimistic or dbOptimistic is to preset the value of the Recordset object's LockEdits property.

****Note **** Setting both the lockedits argument and the options argument to dbReadOnly generates a run-time error.

**See Also **** For more information about optimistic and pessimistic locking, see Chapter 16, "Multiuser Database Solutions," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). For more information about using Recordset objects in client/server applications, see Chapter 14, "Working with the Data Access Components of an Office Solution," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

Creating a Recordset Object from a Form

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

Setvariable**=form.RecordsetClone**

The variable argument is the name of an existing Recordset object. The form argument is the name of an Access form. The following example shows how to assign a Recordset object to the records in the Orders form:

Dim rstOrders As DAO.Recordset

Set rstOrders = Forms!Orders.RecordsetClone

This code always creates the type of Recordset being cloned (the type of Recordset on which the form is based); no other types are available. Note that the Recordset object is declared with the object library qualification. Since Access can now use both DAO and ADO, it is better to fully qualify the data access variables by including the object library reference name.

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.

Creating a Recordset from a Table in a Local Microsoft Access Database

The following example uses the OpenRecordset method to create a table-type Recordset object for a table in the current database:

Dim dbsNorthwind As DAO.Database
Dim rstCustomers As DAO.Recordset

Set dbsNorthwind = CurrentDb
Set rstCustomers = dbsNorthwind.OpenRecordset("Customers")

Notice that you don't need to use the dbOpenTable constant to create a table-type Recordset. If you omit the type constant, as discussed in "Default Recordset Types" earlier in this chapter, DAO chooses the highest-functionality Recordset type available, depending on the object in which the Recordset is created, and the data source. Because the table type is available when you open a Recordset from a local table, DAO uses it.

Creating a Recordset from a Linked Table in a Different Database Format

The following example creates a dynaset-type Recordset object for a linked Paradox® version 3.x table. Because the table type isn't available when you open a Recordset from a linked table in a database other than an Access database, DAO selects the next most efficient type, opening a dynaset-type Recordset.

Dim dbsPublish As DAO.Database
Dim tdfAuthor As DAO.TableDef
Dim rstAuthor As DAO.Recordset

'Get current database.
Set dbsPublish = CurrentDb
Set tdfAuthor = dbsPublish.CreateTableDef("PDXAuthor")

'Connect to the Paradox table Author in the database
'C:\PDX\Publish.
tdfAuthor.Connect = "Paradox 3.X;DATABASE=C:\PDX\Publish"
tdfAuthor.SourceTableName = "Author"

'Link the table.
dbsPublish.TableDefs.Append tdfAuthor

'Create a dynaset-type Recordset for the table.
Set rstAuthor = tdfAuthor.OpenRecordset()

You can also open a Paradox table directly 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 definition can be specified with the Index property.

The following example creates a table-type Recordset object based on the Customers table, by using an existing index called City:

Dim dbsNorthwind As DAO.Database
Dim rstCustomers As DAO.Recordset

Set dbsNorthwind = CurrentDb
Set rstCustomers = dbsNorthwind.OpenRecordset("Customers")

'Move to the first record.
rstCustomers.MoveFirst

'First record with no index set.
MsgBox rstCustomers!CompanyName
rstCustomers.Index = "City"      'Select the City index.
rstCustomers.MoveFirst            'Move to the first record.
MsgBox rstCustomers!CompanyName

rstCustomers.Close
dbsNorthwind.Close
Set rstCustomers = Nothing
Set dbsNorthwind = Nothing

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 by using a query that returns records in a specified order.

****Note **** You must set the Index property before using the Seek method. For information about using the Seek method to locate records that satisfy criteria that you specify, see "Finding a Record in a Table-Type Recordset Object" later in this chapter.

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 current database:

Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset

Set dbsNorthwind = CurrentDb
Set rstProducts = dbsNorthwind.OpenRecordset("Current Product List")

If a stored select query doesn't already exist, the OpenRecordset method also accepts an SQL string instead of the name of a query. The previous example can be rewritten as follows:

Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Dim strSQL As String

Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Products WHERE Discontinued = No " & _
         "ORDER BY ProductName"
Set rstProducts = dbsNorthwind.OpenRecordset(strSQL)

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

****Note **** When you create a Recordset object by using an SQL string or a stored query, your code doesn't continue running until the query returns the first row in the Recordset.

****See Also **** For information about SQL statements, type SQL in the Office Assistant or on the Answer Wizard tab in the Help window and then click Search, or read the "Microsoft Jet SQL Reference" found in Microsoft Access Help (Help menu). For more information about SQL statements, see "Writing SQL Statements" in Chapter 15, "Retrieving and Analyzing Data," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

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. However, you usually want to retrieve records in a specific order. For example, you may want to view invoices arranged by increasing invoice number, or retrieve employee records in alphabetic order by their last names. To see records in a specific order, sort them.

To sort data in a Recordset object that isn't a table, use an SQL ORDER BY clause in the query that constructs the Recordset. You can specify an SQL string when you create a QueryDef object, when you create a stored query in a database, or when you use the OpenRecordset method.

You can also filter data, which means you restrict the result set returned by a query to records that meet some criteria. With any type of Recordset object, use an SQL WHERE clause in the original query to filter data.

The following example opens a dynaset-type Recordset object, and uses an SQL statement to retrieve, filter, and sort records:

Dim dbsNorthwind As DAO.Database
Dim rstManagers As DAO.Recordset

Set dbsNorthwind = CurrentDb
Set rstManagers = dbsNorthwind.OpenRecordset("SELECT FirstName, " & _
                  "LastName FROM Employees WHERE Title = " & _
                  "'Sales Manager' ORDER BY LastName")

One drawback of running an SQL query in an OpenRecordset method is that it has to be recompiled every time you run it. If this query is used frequently, you can improve performance by first creating a stored query using the same SQL statement, and then opening a Recordset object against the query, as shown in the following example:

Dim dbsNorthwind As DAO.Database
Dim rstSalesReps As DAO.Recordset
Dim qdfSalesReps As DAO.QueryDef

Set dbsNorthwind = CurrentDb

Set qdfSalesReps = dbsNorthwind.CreateQueryDef("SalesRepQuery")
qdfSalesReps.SQL = "SELECT * FROM Employees WHERE Title = " & _
                   "'Sales Representative'"

Set rstSalesReps = dbsNorthwind.OpenRecordset("SalesRepQuery")

****Note **** For even greater flexibility and control at run time, you can use query parameters to determine the sort order and filter criteria. For more information, see "Using Parameter Queries" later in this chapter.

Recreating a Query from a Recordset Object

You can also use a Recordset object opened from a QueryDef object as a template to re-create the QueryDef object. To do this, use the CopyQueryDef method. This is useful in situations where a Recordset object variable created from a QueryDef object 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 a dynaset- or snapshot-type Recordset object when you want to run the underlying query again after changing a parameter. This is more convenient than opening a new Recordset, and it runs faster.

The following example creates a Recordset object and passes it to a function that uses the CopyQueryDef method to extract the equivalent SQL string. It then prompts the user to add an additional constraint clause to the query. The code uses the Requery method to run the modified query.

Sub AddQuery()

Dim dbsNorthwind As DAO.Database
Dim qdfSalesReps As DAO.QueryDef
Dim rstSalesReps As DAO.Recordset

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   Set qdfSalesReps = dbsNorthwind.CreateQueryDef("SalesRepQuery")
   qdfSalesReps.SQL = "SELECT * FROM Employees WHERE Title = " & _
                      "'Sales Representative'"

   Set rstSalesReps = qdfSalesReps.OpenRecordset()

   'Call the function to add a constraint.
   AddQueryFilter rstSalesReps

   'Return database to original.
   dbsNorthwind.QueryDefs.Delete "SalesRepQuery"

   rstSalesReps.Close
   qdfSalesReps.Close
   dbsNorthwind.Close

   Set rstSalesReps = Nothing
   Set qdfSalesReps = Nothing
   Set dbsNorthwind = Nothing

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Sub AddQueryFilter(rstData As Recordset)

Dim qdfData As DAO.QueryDef
Dim strNewFilter As String
Dim strRightSQL As String

On Error GoTo ErrorHandler

   Set qdfData = rstData.CopyQueryDef

   'Try "LastName LIKE 'D*'".
   strNewFilter = InputBox("Enter new criteria")

   strRightSQL = Right(qdfData.SQL, 1)

   'Strip characters from the end of the query,
   'as needed.
   Do While strRightSQL = " " Or strRightSQL = ";" Or _
                          strRightSQL = vbCR Or strRightSQL = vbLF
      qdfData.SQL = Left(qdfData.SQL, Len(qdfData.SQL) - 1)
      strRightSQL = Right(qdfData.SQL, 1)
   Loop

   qdfData.SQL = qdfData.SQL & " AND " & strNewFilter
   rstData.Requery qdfData         'Requery the Recordset.
   rstData.MoveLast               'Populate the Recordset.

   '"Lastname LIKE 'D*'" should return 2 records.
   MsgBox "Number of records found:  " & rstData.RecordCount & "."

   qdfData.Close
   Set qdfData = Nothing

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Note **** To use the Requery method, theRestartableproperty of the Recordset object must be set to True. The Restartable property is always set to True when the Recordset is created from a query other than a crosstab query against tables in an Access database. You can't restart SQL pass-through queries. You may or may not be able to restart queries against linked tables in another database format. To determine whether a Recordset object can rerun its query, check the Restartableproperty.

The DAO Sort and Filter Properties

Another approach to sorting and filtering Recordset objects is to set the DAO Sort and Filter properties on an existing Recordset, and then open a new Recordset 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 running it again with the Requery method. The DAO 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 the Recordset has more than 100 records. Using the CopyQueryDef method described in the previous section is preferable.

Moving Through a Recordset Object

A Recordset object usually has a current position, most often at a record. When you refer to the fields in a Recordset, 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 or immediately after the last record. In certain circumstances, the current position is undefined.

****See Also **** For more information about referring to fields, see "Referring to Field Objects" later in this chapter.

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

The MoveFirst method moves to the first record.
The MoveLast method moves to the last record.
The MoveNext method moves to the next record.
The MovePrevious method moves to the previous record.
The Move [n] method moves forward or backward the number of records you specify in its syntax.

You can use each of these methods on table-, dynaset-, and snapshot-type Recordset objects. On a forward-only-type Recordset object, you can use only the MoveNext and Move methods. If you use the Move method on a forward-only-type Recordset, the argument specifying the number of rows to move must be a positive integer.

The following example opens a Recordset object on the Employees table containing all of the records that have a Null value in the ReportsTo field. The function then updates the records to indicate that these employees are temporary employees. For each record in the Recordset, the example changes the Title and Notes fields, and saves the changes with the Update method. It uses the MoveNext method to move to the next record.

Sub UpdateEmployees()

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strSQL As String
Dim intI As Integer

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   'Open a recordset on all records from the Employees table that have
   'a Null value in the ReportsTo field.
   strSQL = "SELECT * FROM Employees WHERE ReportsTo IS NULL"
   Set rstEmployees = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)

   'If the recordset is empty, exit.
   If rstEmployees.EOF Then Exit Sub

   intI = 1
   With rstEmployees
      Do Until .EOF
         .Edit
         ![ReportsTo] = 5
         ![Title] = "Temporary"
         ![Notes] = rstEmployees![Notes] & "Temp #" & intI
         .Update
         .MoveNext
         intI = intI + 1
      Loop
   End With

   RstEmployees.Close
   dbsNorthwind.Close

   Set rstEmployees = Nothing
   Set dbsNorthwind = Nothing

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

****Note **** The previous example is provided only for the purposes of illustrating the Update and MoveNext methods. It would be much faster to perform this bulk operation with an SQL UPDATE query.

Detecting the Limits of a Recordset Object

In a Recordset object, if you try to move beyond the beginning or ending record, a run-time error occurs. For example, if you try to use the MoveNext method when you're already at the last record of the Recordset, 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. If BOF is True, the current position is before the first record in the Recordset. The BOF property is also True if there are no records in the Recordset when it's opened. Similarly, the EOF property is True if the current position is after the last record in the Recordset, or if there are no records.

The following example shows you 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 based on the Orders table from the current database. It moves through the records, first from the beginning of the Recordset to the end, and then from the end of the Recordset to the beginning.

Dim dbsNorthwind As DAO.Database
Dim rstOrders As DAO.Recordset

   Set dbsNorthwind = CurrentDb
   Set rstOrders = dbsNorthwind.OpenRecordset("Orders")

   'Do until ending of file.
   Do Until rstOrders.EOF
      '
      ' Manipulate the data.
      '
      rstOrders.MoveNext            ' Move to the next record.
   Loop

   rstOrders.MoveLast               ' Move to the last record.

   'Do until beginning of file.
   Do Until rstOrders.BOF
      '
      ' Manipulate the data.
      '
      rstOrders.MovePrevious          ' Move to the previous record.
   Loop

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

If the Recordset contains no records when you open it, both BOF and EOF are True.
When BOF or EOF is True, the property remains 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 is deleted, the property remains False until you try to move to another record, at which time both BOF and EOF become True.
At the moment you create or open a Recordset 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 when you use the MovePrevious method, BOF is set to True. If you use MovePrevious 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 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.

The following illustration shows the settings of the BOF and EOF properties for all possible current positions in a Recordset.

Counting the Number of Records in a Recordset Object

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 contains the number of records in a table-type Recordset or the total number of records accessed in a dynaset- or snapshot-type Recordset. A Recordset object with no records has a RecordCount property value of 0.

****Note **** The value of the RecordCount property equals the number of records that have actually been accessed. For example, when you first create a dynaset or snapshot, you have accessed (or visited) only one record. If you check the RecordCount property immediately after creating the dynaset or snapshot (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, then use MoveFirst to return to the first record. This isn't done automatically because it may be slow, especially for large result sets.

When you open a table-type Recordset object, you effectively visit all of the records in the underlying table, and the value of the RecordCount property totals the number of records in the table as soon as the Recordset is opened. Canceled transactions may make the value of the RecordCount property 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 snapshot-type Recordset object, and then determines the number of records in the Recordset:

Function FindRecordCount(strSQL As String) As Long

Dim dbsNorthwind As DAO.Database
Dim rstRecords As DAO.Recordset

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   Set rstRecords = dbsNorthwind.OpenRecordset(strSQL)

   If rstRecords.EOF Then
      FindRecordCount = 0
   Else
      rstRecords.MoveLast
      FindRecordCount = rstRecords.RecordCount
   End If

   rstRecords.Close
   dbsNorthwind.Close

   Set rstRecords = Nothing
   Set dbsNorthwind = Nothing

Exit Function

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

As your application deletes records in a dynaset-type Recordset, 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 is positioned on a deleted record. At that time, the setting of the RecordCount property decreases by one. Using the Requery method on a Recordset, followed by the MoveLast method, sets the RecordCount property to the current total number of records in the Recordset.

A snapshot-type Recordset object is static and the value of its RecordCount property doesn't change when you add or delete records in the snapshot's underlying table.

Finding the Current Position in a Recordset Object

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 relative to 0. However, don't think of this property as a record number; if the current record is undefined, the AbsolutePosition property returns – 1. In addition, there is no assurance that a record will have the same absolute position if the Recordset object is re-created because the order of individual records within a Recordset object isn't guaranteed unless it's created with an SQL statement that includes an ORDER BY clause.

****See Also **** For more information about the AbsolutePosition property, see "Why Use Bookmarks Instead of Record Numbers?" later in this chapter.

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 doesn't reflect the total number of records in the Recordset object until the Recordset has been fully populated, the PercentPosition property only reflects the current record position as a percentage of the number of records that have been accessed since the Recordset was opened. To make sure that the PercentPosition property reflects the current record position relative to the entire Recordset, use the MoveLast and MoveFirst methods immediately after opening the Recordset. This fully populates the Recordset object before you use the PercentPosition property. If you have a large result set, using the MoveLast method may take a long time for Recordsets that aren't of type table.

****Note **** 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 that marks a user's progress while moving though a set of records. For example, you may want a control that indicates the percent of records completed. For more information about the PercentPosition property, search Microsoft Access Help (Help menu) for "PercentPosition property."

The following example opens a Recordset object on a table called Employees. The procedure then moves through the Employees table and uses the SysCmd function to display a progress bar showing the percentage of the table that's been processed. If the hire date of the employee is before Jan. 1, 1993, the text "Senior Staff" is appended to the Notes field.

Sub AddEmployeeNotes()

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strMsg As String
Dim intRet As Integer
Dim intCount As Integer
Dim strSQL As String
Dim sngPercent As Single
Dim varReturn As Variant
Dim lngEmpID() As Long

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   strSQL = "SELECT * FROM Employees"
   Set rstEmployees = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)

   With rstEmployees
      If .EOF Then            ' If no records, exit.
         Exit Sub
      Else
         strMsg = "Processing Employees table..."
         intRet = SysCmd(acSysCmdInitMeter, strMsg, 100)
      End If

      Do Until .EOF
         If !HireDate < #1/1/93# Then
            .Edit
            !Notes = !Notes & ";" & "Senior Staff"
            .Update
         End If

         If .PercentPosition <> 0 Then
            intRet = SysCmd(acSysCmdUpdateMeter, .PercentPosition)
         End If
         .MoveNext
      Loop
   End With

   intRet = SysCmd(acSysCmdRemoveMeter)

   rstEmployees.Close
   dbsNorthwind.Close

   Set rstEmployees = Nothing
   Set dbsNorthwind = Nothing

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
   varReturn = SysCmd(acSysCmdSetStatus, " ")
End Sub

****See Also **** For more information about reading data in a field, see "Reading and Writing Data" later in this chapter.

Finding a Specific Record

The previous section, "Moving Through a Recordset Object," explores ways you can use the Move methods—MoveFirst, MoveLast, MoveNext, MovePrevious, and Move—to loop through records in a Recordset object. However, in most cases 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 of the detail records that belong to a specific order. In these cases, looping through all of the employee or order detail records could be time consuming. Instead, you can use the Seek method with table-type Recordset objects, and the Find methods with dynaset- and snapshot-type Recordset objects to locate records. Since the forward-only-type Recordset object doesn't support the Seek method or any of the Find methods, you cannot search for records in a forward-only-type Recordset.

Finding a Record in a Table-Type Recordset Object

You use the Seek method to locate a record in a table-type Recordset object.

When you use the Seek method to locate a record, the Microsoft Jet database engine uses the table's current index, as defined by the Index property.

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

The syntax for the Seek method is:

table**.Seek**comparison, key1, key2 ...

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

Comparison string	Description
"="	Equal to the specified key values
">="	Greater than or equal to the specified key values
">"	Greater than the specified key values
"<="	Less than or equal to the specified key values
"<"	Less than the specified key values

The keyn arguments are a series of one or more values that correspond to the field or fields that make up the current index of the Recordset. 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 called Employees, and uses the Seek method to locate the record containing a value of lngEmpID in the EmployeeID field. It returns the hire date for the specified employee.

Function GetHireDate(lngEmpID As Long) As Variant

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDB
   Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

   'The index name for Employee ID.
   rstEmployees.Index = "PrimaryKey"
   rstEmployees.Seek "=", lngEmpID

   If rstEmployees.NoMatch Then
      GetHireDate = Null
   Else
      GetHireDate = rstEmployees!HireDate
   End If

   rstEmployees.Close
   dbsNorthwind.Close

   Set rstEmployees = Nothing
   Set dbsNorthwind = Nothing

Exit Function

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

The Seek method always starts searching for records at the beginning of the Recordset object. If you use the Seek method with the same arguments more than once on the same Recordset, it finds the same record.

You can use the NoMatch property on the Recordset object to test whether a record matching the search criteria was found. If the record matching the criteria was found, the NoMatch property will be False; otherwise it will be True.

The following example illustrates how you can create a function that uses the Seek method to locate a record by using a multiple-field index:

Function GetFirstPrice(lngOrderID As Long, lngProductID As Long) As Variant

Dim dbsNorthwind As DAO.Database
Dim rstOrderDetail As DAO.Recordset

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb
   Set rstOrderDetail = dbsNorthwind.OpenRecordset("Order Details")

   rstOrderDetail.Index = "PrimaryKey"
   rstOrderDetail.Seek "=", lngOrderID, lngProductID

   If rstOrderDetail.NoMatch Then
      GetFirstPrice = Null
   Else
      GetFirstPrice = rstOrderDetail!UnitPrice
   End If

   rstOrderDetail.Close
   dbsNorthwind.Close

   Set rstOrderDetail = Nothing
   Set dbsNorthwind = Nothing

Exit Function

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

In this example, the table's primary key consists of two fields: OrderID and ProductID. When you call the GetFirstPrice function with a valid (existing) combination of OrderID and ProductID field values, the function returns the unit price from the found record. If it can't find the combination of field values you want in the table, the function returns the 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 string with the Seek method.

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

You can use the Find methods to locate a record in a dynaset- or snapshot-type Recordset object. DAO provides 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.

****Note **** To locate a record in a table-type Recordset object, use the Seek method, which is described in the previous section.

When you use the Find methods, you specify the search criteria; typically an expression equating a field name with a specific value.

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.

DAO 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.

****See Also **** For more information about bookmarks, see the following section "Marking Record Position with Bookmarks."

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 orders in the Orders table that have no corresponding records in the Order Details table. The function searches for missing orders and, if it finds one, it adds the value in the OrderID field to the array aryOrders().

Function FindOrders() As Variant

Dim dbsNorthwind As DAO.Database
Dim rstOrders As DAO.Recordset
Dim rstOrderDetails As DAO.Recordset
Dim strSQL As String
Dim intIndex As Integer
Dim aryOrders() As Long

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   'Open recordsets on the Orders and Order Details tables. If there are
   'no records in either table, exit the function.
   strSQL = "SELECT * FROM Orders ORDER BY OrderID"
   Set rstOrders = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)
   If rstOrders.EOF Then Exit Function

   strSQL = "SELECT * FROM [Order Details] ORDER BY OrderID"
   Set rstOrderDetails = dbsNorthwind.OpenRecordset(strSQL, _
                         dbOpenSnapshot)

   'For the first record in Orders, find the first matching record
   'in OrderDetails. If no match, redimension the array of order IDs and
   'add the order ID to the array.
   intIndex = 1
   rstOrderDetails.FindFirst "OrderID = " & rstOrders![OrderID]
   If rstOrderDetails.NoMatch Then
      ReDim Preserve aryOrders (1 To intIndex)
      aryOrders (intIndex) = rstOrders![OrderID]
      rstOrders.MoveNext
   End If

   'The first match has already been found, so use the FindNext method to
   'find the next record satisfying the criteria.
   Do Until rstOrders.EOF
      rstOrderDetails.FindNext "OrderID = " & rstOrders![OrderID]
      If rstOrderDetails.NoMatch Then
         intIndex = intIndex + 1
         ReDim Preserve aryOrders (1 To intIndex)
         aryOrders (intIndex) = rstOrders![OrderID]
      End If
      rstOrders.MoveNext
   Loop

   FindOrders = aryOrders

   rstOrders.Close
   rstOrderDetails.Close
   dbsNorthwind.Close

   Set rstOrders = Nothing
   Set rstOrderDetails = Nothing
   Set dbsNorthwind = Nothing

Exit Function

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

****Tip **** If you need to frequently search records in a dynaset, you may find it easier to create a temporary indexed table and use the Seek method instead.

Marking Record Position with Bookmarks

A bookmark is a system-generated Byte array that uniquely identifies each record. The DAO 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 DAO Bookmark property to a variable of type Variant. To return to the record, set the DAO Bookmark property to the value of the variable.

The following example illustrates how you can find the product that makes the most and least revenue per category by using a bookmark to save the current record position. By using a bookmark, you can perform other operations on the Recordset object, and then return to the saved record position.

Sub GetProductStats()

Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Dim rstCategories As DAO.Recordset
Dim varFirstMark As Variant
Dim varHighMark As Variant
Dim varLowMark As Variant
Dim curHighRev As Currency
Dim curLowRev As Currency
Dim strSQL As String
Dim strCriteria As String
Dim strMessage As String

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   strSQL = "SELECT * FROM Products WHERE UnitsOnOrder >= 40 " & _
            "ORDER BY CategoryID, UnitsOnOrder DESC"
   Set rstProducts = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)
   If rstProducts.EOF Then Exit Sub

   StrSQL = "SELECT CategoryID, CategoryName FROM Categories " & _
            "ORDER BY CategoryID"
   Set rstCategories = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)

   'For each category find the product generating the least revenue
   'and the product generating the most revenue.
   Do Until rstCategories.EOF

      strCriteria = "CategoryID = " & rstCategories![CategoryID]
      rstProducts.FindFirst strCriteria
      curHighRev = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]

      If Not rstProducts.NoMatch Then

         'Set bookmarks at the first record containing the CategoryID.
         varFirstMark = rstProducts.Bookmark
         varHighMark = varFirstMark
         varLowMark = varFirstMark

         'Find the product generating the most revenue.
         Do While rstProducts![CategoryID] = rstCategories![CategoryID]
            If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] > _
            curHighRev Then
               curHighRev = rstProducts![UnitPrice] * _
                            rstProducts![UnitsOnOrder]
               varHighMark = rstProducts.Bookmark
            End If
            rstProducts.MoveNext
         Loop

         'Move to the first record containing the CategoryID.
         rstProducts.Bookmark = varFirstMark
         curLowRev = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]

         ' Find the product generating the least revenue.
         Do While rstProducts![CategoryID] = rstCategories![CategoryID]
            If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] < _
            curLowRev Then
               curLowRev = rstProducts![UnitPrice] * _
                           rstProducts![UnitsOnOrder]
               varLowMark = rstProducts.Bookmark
            End If
            rstProducts.MoveNext
         Loop

      End If

      'Set high & low bookmarks to build the message string.
      strMessage = "CATEGORY:  " & rstCategories!CategoryName & _
                   vbCrLf & vbCrLf
      rstProducts.Bookmark = varHighMark
      strMessage = strMessage & "HIGH: $" & curHighRev & "  " & _
                   rstProducts!ProductName & vbCrLf
      rstProducts.Bookmark = varLowMark
      strMessage = strMessage & "LOW:  $" & curLowRev & "  " & _
                   rstProducts!ProductName
      MsgBox strMessage, , "Product Statistics"
      rstCategories.MoveNext
   Loop

   rstProducts.Close
   rstCategories.Close
   dbsNorthwind.Close

   Set rstProducts = Nothing
   Set rstCategories = Nothing
   Set dbsNorthwind = Nothing

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

A bookmark is particularly useful if a method fails because the current record position is undefined.

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

rstCustomers.Bookmark = rstCustomers.LastModified

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

****See Also **** For more information about adding new records, see "Adding a New Record" later in this chapter.

Bookmark Scope

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

Dim dbsNorthwind As DAO.Database
Dim rstOriginal As DAO.Recordset
Dim rstDuplicate As DAO.Recordset
Dim varBookMark As Variant

   Set dbsNorthwind = CurrentDb

   'Create the first Recordset.
   Set rstOriginal = dbsNorthwind.OpenRecordset("Orders", dbOpenDynaset)

   'Save the current record position.
   varBookMark = rstOriginal.Bookmark

   'Create a duplicate Recordset.
   Set rstDuplicate = rstOriginal.Clone()

   'Go to same record.
   rstDuplicate.Bookmark = varBookMark

   rstOriginal.Close

You can also use the DAO 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 that is 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 cmdShowSuper_Click()

Dim frmEmployees As Form
Dim rstEmployees As DAO.Recordset
Dim varBookMark As Variant
Dim strEmployee As String
Dim strSuper As String

   Set frmEmployees = Screen.ActiveForm

   'Open the Recordset.
   Set rstEmployees = frmEmployees.RecordsetClone

   varBookMark = frmEmployees.Bookmark
   strEmployee = frmEmployees!FirstName & " " & frmEmployees!LastName
   rstEmployees.FindFirst "EmployeeID = " & frmEmployees!ReportsTo

   If rstEmployees.NoMatch Then
      MsgBox "Couldn't find " & strEmployee & "'s supervisor."
   Else
      frmEmployees.Bookmark = rstEmployees.Bookmark
      strSuper = frmEmployees!FirstName & " " & frmEmployees!LastName
      MsgBox strEmployee & "'s supervisor is " & strSuper
      frmEmployees.Bookmark = varBookMark
   End If

   rstEmployees.Close

   Set rstEmployees = Nothing
   Set frmEmployees = Nothing

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 Access databases, your view of records (a Recordset) is usually a subset of the records in one or more tables. Because the actual number of records in a Recordset 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 isn't 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 was created with a query that includes an ORDER BY clause, or is a table-type Recordset with an index. Record numbers are usually meaningless in a Recordset object.

Instead of record numbers, DAO provides bookmarks to uniquely identify a particular record. A given record retains its unique bookmark for the life of the Recordset.

Which Recordset Objects Don't Support Bookmarks?

Dynasets based on certain linked tables, such as Paradox tables that have no primary key, don't support bookmarks, nor do forward-only-type Recordset objects.

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

****Note **** 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 snapshot-type or forward-only-type Recordset object.

This section presents the methods and procedures for changing data in table- and dynaset-type Recordset objects.

Using Parameter Queries

A parameter query is a query that when run displays a dialog box that prompts the user for information, such as criteria for retrieving records or a value to insert in a field. You can use stored parameter queries to accomplish most of the database maintenance tasks described in the rest of this chapter.

In many situations, you'll want a user or another procedure to provide parameters you can use with your stored queries and Recordset objects. Microsoft Jet provides the means to do this. First, create a stored query, specifying which parameters the user needs to provide. When you open a Recordset against one of these queries, 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 takes two strings that represent dates and creates an unstored parameter query that returns all records in the Orders table whose order date is between the two dates. It adds all values in the OrderID field in the query's Recordset to an array.

Function OrdersFromTo(strDateFrom As Variant, strDateTo As Variant) _
         As Variant

Dim dbsNorthwind As DAO.Database
Dim rstOrders As DAO.Recordset
Dim qdfOrders As DAO.QueryDef
Dim strSQL As String
Dim intPosition As Integer
Dim aryOrderIDs() As Long

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb
   strSQL = "PARAMETERS [DateFrom] DateTime, [DateTo] DateTime; "
   strSQL = strSQL & "SELECT * FROM Orders WHERE OrderDate BETWEEN "
   strSQL = strSQL & "[DateFrom] AND [DateTo]"

   'Create an unstored parameter query.
   Set qdfOrders = dbsNorthwind.CreateQueryDef("", strSQL)

   'Set the query parameters.
   qdfOrders.Parameters("DateFrom") = strDateFrom
   qdfOrders.Parameters("DateTo") = strDateTo

   'Open a forward-only snapshot on the query.
   Set rstOrders = qdfOrders.OpenRecordset(dbOpenSnapshot, dbForwardOnly)

   'Load all the OrderIDs in the query into an array that the caller
   'of the function can use.
   intPosition = 1
   Do While rstOrders.EOF = False
      ReDim Preserve aryOrderIDs(1 To intPosition)
      aryOrderIDs(intPosition) = rstOrders!OrderID
      intPosition = intPosition + 1
      rstOrders.MoveNext
   Loop

   OrdersFromTo = aryOrderIDs()

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

****See Also **** For more information about parameter queries, type create parameter queries in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Making Bulk Changes

Many of the changes you may otherwise perform in a loop can be done more efficiently with an update or delete query using SQL. The following example creates a QueryDef object to update the Employees table and then runs the query:

Dim dbsNorthwind As DAO.Database
Dim qdfChangeTitles As DAO.QueryDef

   Set dbsNorthwind = CurrentDb
   Set qdfChangeTitles = dbsNorthwind.CreateQueryDef("")

   qdfChangeTitles.SQL = "UPDATE Employees SET Title = " & _
                         "'Account Executive' WHERE Title = " & _
                         "'Sales Representative'"

   'Invoke query.
   qdfChangeTitles.Execute dbFailOnError

You can replace the entire SQL string in this example with a stored parameter query, in which case the procedure would prompt the user for parameter values. The following example shows how the previous example may be rewritten as a stored parameter query:

Dim dbsNorthwind As DAO.Database
Dim qdfChangeTitles As DAO.QueryDef
Dim strSQLUpdate As String
Dim strOld As String
Dim strNew As String

   Set dbsNorthwind = CurrentDb

   strSQLUpdate = "PARAMETERS [Old Title] Text, [New Title] Text; " & _
                  "UPDATE Employees SET Title = [New Title] WHERE " & _
                  "Title = [Old Title]"

   'Create the unstored QueryDef object.
   Set qdfChangeTitles = dbsNorthwind.CreateQueryDef("", strSQLUpdate)

   'Prompt for old title.
   strOld = InputBox("Enter old job title:")

   'Prompt for new title.
   strNew = InputBox("Enter new job title:")

   'Set parameters.
   qdfChangeTitles.Parameters("Old Title") = strOld
   qdfChangeTitles.Parameters("New Title") = strNew

   'Invoke query.
   qdfChangeTitles.Execute

****Note **** A delete query is much more efficient than code that loops through a Recordset modifying or deleting records one at a time.

Modifying an Existing Record

You can modify existing records in a table or dynaset-type Recordset object by using the Edit and Update methods.

To modify an existing record in a table- or dynaset-type Recordset object

Go to the record that you want to change.
Use the Edit method to prepare the current record for editing.
Make the necessary changes to the record.
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:

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset

   Set dbsNorthwind = CurrentDb
   Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

   rstEmployees.MoveFirst
   Do Until rstEmployees.EOF
      If rstEmployees!Title = "Sales Representative" Then
         rstEmployees.Edit
         rstEmployees!Title = "Account Executive"
         rstEmployees.Update
      End If
      rstEmployees.MoveNext
   Loop

****Note **** If you don't use the Edit method before you try to change a value in the current record, a run-time error occurs. If you edit the current record and then move to another record or close the Recordset object without first using the Update method, your changes are lost without warning. For example, omitting the Update method from the preceding example results in no changes being made to the Employees table.

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 isn't practical when the current record is the first or last record in the Recordset, or is a new record. It's generally simpler to use the CancelUpdate method.

Inconsistent Updates

Dynaset-type Recordset objects can be based on a multiple-table query containing tables with 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. For example:

Set

 rstTotalSales = dbs.OpenRecordset("Sales Totals" ,,dbInconsistent)

When you update an inconsistent dynaset, you can easily destroy the referential 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 to 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-type Recordset objects, but no compile or run-time error is returned if the dbInconsistent constant is used with those types of Recordset objects.

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

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-type Recordset object. The following example deletes all the duplicate records in the Shippers table:

Sub DeleteDuplicateShippers()

Dim dbsNorthwind As DAO.Database
Dim rstShippers As DAO.Recordset
Dim strSQL As String
Dim strName As String

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb
   strSQL = "SELECT * FROM Shippers ORDER BY CompanyName, ShipperID"
   Set rstShippers = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)

   'If no records in Shippers table, exit.
   If rstShippers.EOF Then Exit Sub

   strName = rstShippers![CompanyName]
   rstShippers.MoveNext

   Do Until rstShippers.EOF
      If rstShippers![CompanyName] = strName Then
         rstShippers.Delete
      Else
         strName = rstShippers![CompanyName]
      End If
      rstShippers.MoveNext
   Loop

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

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. However, keep in mind 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 on a table-type Recordset, you'll get error 3167, "Record is deleted." On a dynaset, you'll get error 3021, "No current record."

If you have a Recordset clone positioned at the deleted record and you 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

You can add a new record to a table- or dynaset-type Recordset object by using the AddNew method.

To add a new record to a table- or dynaset-type Recordset object

Use the AddNew method to create a new record you can edit.
Assign values to each of the record's fields.
Use the Update method to save the new record.

The following example adds a new record to a table-type Recordset called Shippers:

Dim dbsNorthwind As DAO.Database
Dim rstShippers As DAO.Recordset

   Set dbsNorthwind = CurrentDb
   Set rstShippers = dbsNorthwind.OpenRecordset("Shippers")

   rstShippers.AddNew
   rstShippers!CompanyName = "Global Parcel Service"
      .
      . ' Set remaining fields.
      .

   rstShippers.Update

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 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, the new record appears at the end of the Recordset, no matter how the Recordset is sorted. To force the new record to appear in its properly sorted position, you can either use the Requery method or re-create the Recordset object.

If you add a record to a table-type Recordset, the record appears positioned according to the current index, or at the end of the table if there is no current index. Because Microsoft Jet version 3.0 or later allows multiple users to create new records in a table simultaneously, your record may not appear right at the end of the Recordset 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.

****Note **** If you use the AddNew method to add a new record, and then move to another record or close the Recordset object without first using the Update method, your changes are lost without warning. For example, omitting the Update method from the preceding example results in no changes being made to the Shippers table.

Caching ODBC Data with a Recordset

You can use the dynaset-type Recordset to create a local cache for ODBC data. This 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, thus improving performance.

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

Tracking Recordset Changes

You may need to determine when the underlying TableDef object of a table-type Recordset 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. These properties are only updated when the table's design changes; they aren't affected by changes to records in the table.

Microsoft Jet Transactions

A transaction is a set of operations bundled together and treated as a single unit of work. The work in a transaction must be completed as a whole; if any part of the transaction fails, the entire transaction fails. Transactions offer the developer the ability to enforce data integrity. With multiple database operations bundled into a single unit that must succeed or fail as a whole, the database can't reach an inconsistent state. Transactions are common to most database management systems.

The most common example of transaction processing involves a bank's automated teller machine (ATM). 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 isn't 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.

You should consider using transactions if you want to make sure that each operation in a group of operations is successful before all operations are committed. Keep in mind 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.

Note **** The behavior of transactions with Access databases differs from the behavior of ODBC data sources, such as Microsoft SQL Server™. For example, if a database is connected to a file server, and the file server stops before a transaction has had time to commit its changes, then your database could be left in an inconsistent state. If you require true transaction support with respect to durability, you should investigate the use of a client/server architecture. For more information about client/server architecture, see Chapter 16, "Multiuser Database Solutions," in the*****Microsoft Office 2000/Visual Basic Programmer's Guide* (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

Using Transactions in Your Applications

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

Method	Operation
workspace.BeginTrans	Begins the transaction
workspace.CommitTrans	Posts the transaction, writing its updates to the permanent database objects
workspace.Rollback	Cancels the transaction

The following example changes the job title of all sales representatives in the Employees table of the Northwind sample 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. Notice that you can use the Rollback method to undo changes that you saved with the Update method.

Sub ChangeTitle()

Dim wrkCurrent As DAO.Workspace
Dim dbsNorthwind As DAO.Database
Dim rstEmployee As DAO.Recordset

On Error GoTo ErrorHandler

   Set wrkCurrent = DBEngine.Workspaces(0)
   Set dbsNorthwind = CurrentDB
   Set rstEmployee = dbsNorthwind.OpenRecordset("Employees")

   wrkCurrent.BeginTrans
   Do Until rstEmployee.EOF
      If rstEmployee!Title = "Sales Representative" Then
         rstEmployee.Edit
         rstEmloyee!Title = "Sales Associate"
         rstEmployee.Update
      End If
      rstEmployee.MoveNext
   Loop

   If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
      wrkCurrent.CommitTrans
   Else
      wrkCurrent.Rollback
   End If

   rstEmployee.Close
   dbsNorthwind.Close
   wrkCurrent.Close

   Set rstEmployee = nothing
   Set dbsNorthwind = Nothing
   Set wrkCurrent = Nothing

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

When you use transactions, all databases and Recordset objects in the specified Workspace object are affected—transactions are global to the workspace, not to a specific database or Recordset. If you perform operations on more than one database or within a workspace transaction, the Commit and Rollback methods affect all the objects changed within that workspace during the transaction.

****Note **** You can also use the BeginTrans, CommitTrans, and Rollback methods with the DBEngine object. In this case, the transaction is applied to the default workspace, which is

DBEngine.Workspaces(0)

Managing Transactions

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

Transaction Size

Transaction size is limited only by the amount of physical space on your disk drive. That is, Microsoft Jet can store a quantity of transaction data as large as the amount of free space on your disk drive. If the available disk space is exhausted during a transaction, a trappable run-time error occurs. Your code should check for this error (number 2004) and react accordingly. If you try to commit the transaction after this error occurs, Microsoft Jet will commit an indeterminate number of changes, possibly leaving the database in an inconsistent state. You should usually roll back the transaction when this error occurs to ensure a consistent database state.

Nesting Transactions

You can have up to five levels of transactions active at any one time by nesting combinations of BeginTrans and either CommitTrans or Rollback. If you nest transactions, you must make sure that 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 transactions with overlapping, nonnested scopes, you can open additional Workspace objects and manage other transactions within those new workspaces.

When a Transaction is Rolled Back by the Jet Database Engine

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

Transactions on External Data Sources

Transactions aren't 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 doesn't fail or generate a run-time error, but it doesn't actually do anything either.

****Note **** Microsoft Access version 2.0 databases are opened by Microsoft Access for Windows 95, Microsoft Access 97, and Microsoft Access 2000 as external installable ISAM databases. However, unlike other external data sources, the Jet database engine does support transactions on Microsoft Access version 2.x databases.

To determine whether or not a Database or Recordset object supports transactions, you can check the value of its Transactions property. A value of True indicates that the object does support transactions, and a value of False indicates that the object doesn't support transactions.

Transactions and Performance

In previous versions of Access, it was generally recommended that you use transactions as a performance enhancement. Now all transactions for DAO add, update, and delete operations are performed internally and automatically. 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 transaction behavior. You can do this by creating and modifying various settings in the Microsoft® Windows® Registry.

****See Also **** For information about tuning Registry settings used by Microsoft Jet, see "Adjusting Windows Registry Settings to Improve Performance" in Chapter 7, "Optimizing Your Application."

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.

Copying a Single Field

You can copy a single field of a record to a variable of the appropriate data type. The following example extracts three fields from the first record in a Recordset object:

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strFirstName As String
Dim strLastName As String
Dim strTitle As String

   Set dbsNorthwind = CurrentDb
   Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

   rstEmployees.MoveFirst
   strFirstName = rstEmployees!FirstName
   strLastName = rstEmployees!LastName
   strTitle = rstEmployees!Title

****See Also **** For more information about extracting data from a record, see "Reading and Writing Data" later in this chapter.

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 identifies the field and the second identifies the row number, as follows:

varRecords(intField, intRecord)

The following example uses an SQL statement to retrieve three fields from a table called Employees into a Recordset object. It then uses the GetRows method to retrieve the first three records of the Recordset, and it stores the selected records in a two-dimensional array. It then prints each record, 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 practice, it would be more reliable to use two loops, one nested in the other, and to provide integer variables for the indexes that step through both dimensions of the array.

Sub GetRowsTest()

Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim varRecords As Variant
Dim intNumReturned As Integer
Dim intNumColumns As Integer
Dim intColumn As Integer
Dim intRow As Integer
Dim strSQL As String

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb
   strSQL = "SELECT FirstName, LastName, Title FROM Employees"
   Set rstEmployees = dbsNorthwind.OpenRecordset(SQL, dbOpenSnapshot)

   varRecords = rstEmployees.GetRows(3)
   intNumReturned = UBound(varRecords, 2) + 1
   intNumColumns = UBound(varRecords, 1) + 1

   For intRow = 0 To intNumReturned - 1
      For intColumn = 0 To intNumColumns - 1
         Debug.Print varRecords(intColumn, intRow)
      Next intColumn
   Next intRow

   rstEmployees.Close
   dbsNorthwind.Close

   Set rstEmployees = Nothing
   Set dbsNorthwind = Nothing

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

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, you can see why this approach is much faster than copying one field at a time.

Notice also that you don't have to declare the Variant 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 rows by using multiple GetRows calls, use the EOF property to be sure that you're at the end of the Recordset. The GetRows method may return fewer rows than you request. If you request more that the remaining number of rows in a Recordset, for example, the GetRows method only returns the rows that remain. Similarly, if it can't retrieve a row in the range requested, it doesn't return that row. 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 doesn't generate a run-time error. This situation may occur if a record in a dynaset was deleted by another user. If it returns fewer records than the number requested and you're not at the end of the file, you 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 that you need. This is especially important for OLE Object and Memo fields.

****See Also **** For more information about OLE Object and Memo fields, see "The OLE Object and Memo Data Types" later in this chapter.

Using Field Objects

The default collection of a Recordset object is its Fields collection. This collection includes a single Field object that corresponds to each field (or column) in the Recordset. Each Field object has a set of properties that uniquely identifies the field name, data type, and so on, as well as the value of the field in the current record. You use the Field objects in a Recordset object to read and set values for the fields in the current record of the Recordset object.

You manipulate a field by using a Field object and its methods and properties. For example, you can:

Use the OrdinalPosition property to get or set the position of a Field object relative to other fields in a Fields collection.
Use the FieldSize property, the GetChunk method, or the AppendChunk method to get or set a value in an OLE Object or Memo field of a Recordset object.
Read or set the DAO Value property of a Recordset object.
Read or set the DAO AllowZeroLength, Required, ValidationRule, ValidationText, or ValidateOnSet property setting to find or specify validation conditions.
Read the SourceField and SourceTable property settings to determine the original source of the data.

Referring to Field Objects

You can identify a Field object by its DAO Name property, which corresponds to the column name in the table from which the data in the field was retrieved. The Fields collection is the default collection of a Recordset object. Therefore, you can refer to the LastName field in the rstEmployees Recordset in any of the following ways:

rstEmployees.Fields("LastName")
rstEmployees!LastName
rstEmployees![LastName]

When using the ! operator, you must include brackets around a field name when it contains spaces. For example, the statement:

strEmployee = rstEmployees!Last Name

will not compile, but the statement:

strEmployee = rstEmployees![Last Name]

will compile with no problems.

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

rstEmployees.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. Objects in a collection are numbered starting with zero, so the first Field object in the Fields 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 retrieved when the Recordset object is opened. One drawback to this approach is that you can't be certain which field will be referred to, because the underlying table structure may change, fields may be added or deleted, and so on.

To help you determine the order of fields in a Fields collection, the Field object supports the OrdinalPosition property, which you can use to get or set a field's position relative to other fields in the collection. You can set the OrdinalPosition property to any positive integer to change the field order when data is displayed in a form, copied to an array or a Microsoft Excel worksheet, and so on.

When writing code that refers to fields within a loop, it's more efficient to refer to Field objects rather than to refer to fields by their names. The following example shows a more efficient way of writing the ChangeTitle procedure discussed earlier in this chapter. Instead of referring to the Title field as

rstEmployees!Title

, it refers to the field by its object variable, which doesn't require that the field be looked up in the Fields collection every time it's referred to.

Sub ChangeTitle()

Dim wrkCurrent As DAO.Workspace
Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim fldTitle As DAO.Field

On Error GoTo ErrorHandler

   Set wrkCurrent = DBEngine.Workspaces(0)
   Set dbsNorthwind = CurrentDB
   Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

   Set fldTitle = rstEmployees.Fields("Title")
   wrkCurrent.BeginTrans

   Do Until rstEmployees.EOF
      If fldTitle = "Sales Representative" Then
         rstEmployees.Edit
         fldTitle = "Sales Associate"
         rstEmployees.Update
      End If

      rstEmployees.MoveNext
   Loop

   If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
      wrkCurrent.CommitTrans
   Else
      wrkCurrent.Rollback
   End If

   rstEmployees.Close
   dbsNorthwind.Close
   wrkCurrent.Close

   Set fldTitle = Nothing
   Set rstEmployees = Nothing
   Set dbsNorthwind = Nothing
   Set wrkCurrent = Nothing

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Field Data Types

A Field object has a Type property that can be set to one of the following 12 DAO data types.

Type property setting	Data type
dbBoolean	Boolean
dbByte	Byte
dbInteger	Integer
dbLong	Long
dbCurrency	Currency
dbSingle	Single
dbDouble	Double
dbDate	Date/Time
dbText	Text
dbLongBinary	OLE Object
dbMemo	Memo
dbGUID	GUID

For a Field object on a Recordset, the Type property is read-only. However, you must be aware of the field type when copying data to or from a field in code or a "Type mismatch" error may occur. For example, you cannot copy Text data to an Integer field.

The Type property of a Field object on a Recordset is determined by the underlying table from which the record was retrieved. If you create the table and its fields by using DAO data-definition language (DDL) statements, you can easily determine the data type of the table's fields.

If you're accessing external data through an installable ISAM driver, the data types within external tables may be different from those defined within Microsoft Jet. The installable ISAM driver for the external data source converts external data types into their closest equivalent DAO data type.

The GUID Data Type

The GUID data type is used to store a globally unique identifier, a unique string of digits that identifies OLE objects, Microsoft SQL Server remote procedure calls, and other entities that need a unique reference identification.

****Note **** The GUID data type is also used in the Database object's ReplicaID property to identify a replica.

The Text Data Type

For Field objects declared as type Text, you must set the Size property, which indicates the length of the longest string that can be stored in the field. All other types of Field objects have their Size property set automatically.

The Currency Data Type

If you need to store monetary values, use fields of type Currency. Don't use any of the number data types (such as Single) for currency values, because numbers to the right of the decimal may be rounded during calculations. The Currency data type always maintains a fixed number of digits to the right of the decimal.

The Long Data Type

In some tables, you'll want to store a series of sequential numbers to uniquely identify records. For example, you may want to start customer order records at order number 1 and begin counting upward.

Access can automatically insert unique numbers in a field, saving your application the effort of generating unique identifiers to be used within a primary key field. To take advantage of this capability, define a field with the Long data type and set the dbAutoIncrField constant in the Field object's Attributes property. Auto-incrementing fields start at 1 and increment sequentially. Fields of this type are also referred to as AutoNumberfields.

If you want to establish a primary key/foreign key relationship between two tables by using an AutoNumber field, make sure that the foreign key field is also defined as Long.

You can also set the DAO DefaultValue property of a Field object on a TableDef object to a special value called GenUniqueId( ). This causes a random number to be assigned to this field whenever a new record is added or created. The field's Type property must be Long.

****Note **** A Field object's data type is read/write before the field is appended to a table's Fields collection, and read-only after it's appended.

The OLE Object and Memo Data Types

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. OLE****Object fields consist of binary data up to 1.2 gigabytes in size. This type is used to store pictures, files, or other raw binary data. Memo fields are used to store lengthy text and numbers, such as comments or explanations. The size of a Memo field is limited by the maximum size of the database.

Records within a Recordset object must fit on the 2K data pages supported by the Microsoft Jet database engine. Each Field object you include in the table definition counts toward this 2K total, including OLE Object and Memo fields. However, the amount stored for OLE Object and Memo fields is only 14 bytes per non-null field, and only 1 byte for null****fields. The 14 bytes is used to store a pointer to the actual data for these fields, which is located on additional 2K pages. The amount of data committed to each text field isn't set until you actually store data in the field. You can overcommit a data page by defining more text fields than there would be room for, but no more than about 2K of actual data can be stored in a record. For example, you can define fifteen 250-byte text fields in a record, but the total number of characters stored must be less than 2K.

When you query tables containing large value fields, don't include those fields in the field list unless you need them, because returning large value fields takes time. Also, be aware that you can't index large value fields.

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

Handling Large Value Data

Sometimes you'll need to read or copy data from a large value field where you don't have sufficient memory to copy the entire field in a single statement. Instead, you have to break up the data into smaller units, or chunks, that will fit available memory. The FieldSize property 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 GetChunk and AppendChunk until the entire field is copied.

****See Also **** For more information about data types, type data types in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Reading and Writing Data

When you read or write data to a field, you're actually reading or setting the DAO Value property of a Field object. The DAO Value property is the default property of a Field object. Therefore, you can set the DAO Value property of the LastName field in the rstEmployees Recordset in any of the following ways:

rstEmployees!LastName.Value = strName
rstEmployees!LastName = strName
rstEmployees![LastName] = strName

Write Permission

The tables underlying a Recordset object may not permit you to modify data, even though the Recordset is of type dynaset or table, which are usually updateable. Check the Updateable property of the Recordset to determine whether its data can be changed. If the property is True, the Recordset object can be updated.

Individual fields within an updateable Recordset object may not be updateable, and trying to write to these fields generates a run-time error. To determine whether a given field is updateable, check the DataUpdatable property of the corresponding Field object in the Fields collection of the Recordset. The following example returns True if all fields in the dynaset created by strQuery are updateable and returns False otherwise.

Function RecordsetUpdatable(strSQL As String) As Boolean

Dim dbsNorthwind As DAO.Database
Dim rstDynaset As DAO.Recordset
Dim intPosition As Integer

On Error GoTo ErrorHandler

   'Initialize the function's return value to True.
   RecordsetUpdatable = True

   Set dbsNorthwind = CurrentDb
   Set rstDynaset = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)

   'If the entire dynaset isn't updatable, return False.
   If rstDynaset.Updatable = False Then
      RecordsetUpdatable = False
   Else
      'If the dynaset is updatable, check if all fields in the
      'dynaset are updatable. If one of the fields isn't updatable,
      'return False.
      For intPosition = 0 To rstDynaset.Fields.Count - 1
         If rstDynaset.Fields(intPosition).DataUpdatable = False Then
            RecordsetUpdatable = False
            Exit For
         End If
      Next intPosition
   End If

   rstDynaset.Close
   dbsNorthwind.Close

   Set rstDynaset = Nothing
   Set dbsNorthwind = Nothing

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

Criteria

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

****See Also **** For more information about the DAO AllowZeroLength and Required properties, type AllowZeroLength property or Required property in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Field-Level Data Validation

Validation is the process of determining whether data entered into a field's DAO Value property is within an acceptable range. A Field object on a Recordset may have the DAO ValidationRule and ValidationText properties set. The DAO ValidationRule property is simply a criteria expression, similar to the criteria of an SQL WHERE clause, without the WHERE keyword. The DAO ValidationText property is a string that Access displays in an error message if you try to enter data in the field that's outside the limits of the DAO ValidationRule property. If you're using DAO in your code, then you can use the DAO ValidationText for a message that you want to display to the user.

Note **** The DAO ValidationRule and ValidationText properties also exist at the Recordsetlevel. These are read-only properties, reflecting the table-level validation scheme established on the table from which the current record is retrieved.

A Field object on a Recordset also features the ValidateOnSet property. When the ValidateOnSet property is set to True, Access checks validation as soon as the field's DAO Value property is set. When it's set to False (the default), Access checks validation only when the completed record is updated. For example, if you're adding data to a record that contains a large Memo or OLE Object field and that has the DAO ValidationRule property set, you should determine whether the new data violates the validation rule before trying to write the data—you should write the data when the field value is set. To do so, set the ValidateOnSet property to True. If you wait to check validation until the entire record is written to disk, you may waste time trying to write an invalid record to disk.

Tracing the Origin of Dynaset Fields

A dynaset-type Recordset object can include records from more than one source table. Also, within a single record, fields from different tables can be joined into new records. Sometimes it's useful to know the table from which a field originated. The SourceTable property of a Field object returns the name of the table from which the field's current data was retrieved.

Within a query, a field can be renamed for display purposes. For example, in an SQL SELECT query, the AS operator in the select field list can create an alias for one of the returned fields. In a Recordset based on an SQL query, a field that has been aliased is represented by a Field object whose DAO Name property reflects the alias, not the original field name. To find out the original field name, check the Field object's SourceField property.

Share via

Working with Records and Fields

Working with Records and Fields

Using Recordset Objects

Creating a Recordset Object Variable

Default Recordset Types

OpenRecordset Options

Creating a Recordset Object from a Form

Creating a Recordset Object from a Table

Creating a Recordset from a Table in a Local Microsoft Access Database

Creating a Recordset from a Linked Table in a Different Database Format

Using an Index on a Table-Type Recordset Object

Creating a Recordset Object from a Query

Sorting and Filtering Records

Recreating a Query from a Recordset Object

Modifying a Query from a Recordset Object

The DAO Sort and Filter Properties

Moving Through a Recordset Object

Detecting the Limits of a Recordset Object

Counting the Number of Records in a Recordset Object

Finding the Current Position in a Recordset Object

Finding a Specific Record

Finding a Record in a Table-Type Recordset Object

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

Marking Record Position with Bookmarks

Bookmark Scope

Why Use Bookmarks Instead of Record Numbers?

Which Recordset Objects Don't Support Bookmarks?

Changing Data

Using Parameter Queries

Making Bulk Changes

Modifying an Existing Record

Inconsistent Updates

Deleting an Existing Record

Adding a New Record

Caching ODBC Data with a Recordset

Tracking Recordset Changes

Microsoft Jet Transactions

Using Transactions in Your Applications

Managing Transactions

Transaction Size

Nesting Transactions

When a Transaction is Rolled Back by the Jet Database Engine

Transactions on External Data Sources

Transactions and Performance

Extracting Data from a Record

Copying a Single Field

Copying Entire Records to an Array

Using Field Objects

Referring to Field Objects

Field Data Types

The GUID Data Type

The Text Data Type

The Currency Data Type

The Long Data Type

The OLE Object and Memo Data Types

Handling Large Value Data

Reading and Writing Data

Write Permission

Criteria

Field-Level Data Validation

Tracing the Origin of Dynaset Fields

Additional resources