CHAPTER  9

Working with Records and Fields

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

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 objectstable, dynaset, snapshot, and forward-onlydiffer from each other in significant ways:

 A table-type Recordset object can be created from a table in a Microsoft 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 updatable joins are a unique feature of dynasetsthey enable you to use updatable 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 on 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:

Set variable = database.OpenRecordset (source [, type [, options [, lockedits ]]])

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

Set variable = 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 = dbs.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 a Microsoft 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 updatable (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 on 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.

 dbOptimistic   Microsoft Jet uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment.

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.

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

See Also   For more information on optimistic and pessimistic locking, see Chapter 10, "Creating Multiuser Applications." For information on using Recordset objects in client/server applications, see Chapter 19, "Developing Client/Server Applications."

Creating a Recordset Object from a Form

You can create a Recordset object based on a Microsoft Access form. To do so, use the RecordsetClone property 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 RecordsetClone property is the equivalent of creating a dynaset with the same query. You can use the RecordsetClone property when you want to apply a method that can't be used with forms, such as the FindFirst method. The RecordsetClone property provides access to all the methods and properties that you can use with a dynaset. The syntax for the RecordsetClone property is:

Set variable = form.RecordsetClone

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

Dim rstOrders As 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.

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 dbs As Database, rstCustomers As Recordset
Set dbs = CurrentDb
Set rstCustomers = dbs.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.

See Also   For information on connecting to data in external databases, see Chapter 18, "Accessing External Data."

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 a Microsoft Access database, DAO selects the next most efficient type, opening a dynaset-type Recordset.

Dim dbs As Database
Dim tdf As TableDef
Dim rstTableData As Recordset
' Get current database.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("PDXAuthor")
' Connect to the Paradox table Author in the database
' C:\PDX\Publish.
tdf.Connect = "Paradox 3.X;DATABASE=C:\PDX\Publish"
tdf.SourceTableName = "Author"
' Link the table.
dbs.TableDefs.Append tdf
' Create a dynaset-type Recordset for the table.
Set rstTableData = tdf.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 dbs As Database, rstTableData As Recordset
Set dbs = CurrentDb
Set rstTableData = dbs.OpenRecordset("Customers", dbOpenTable)
' Move to the first record.
rstTableData.MoveFirst
' First record with no index set.
MsgBox rstTableData!CompanyName
rstTableData.Index = "City"       ' Select the City index.
rstTableData.MoveFirst            ' Move to the first record.
MsgBox rstTableData!CompanyName
rstTableData.Close

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.

Important   You must set the Index property before using the Seek method. For information on 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 dbs As Database, rstProducts As Recordset
Set dbs = CurrentDb
Set rstProducts = dbs.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 dbs As Database, rstProducts As Recordset
Dim strQuerySQL As String
Set dbs = CurrentDb
strQuerySQL = "SELECT * FROM Products WHERE Discontinued = No " _
   & "ORDER BY ProductName;"
Set rstProducts = dbs.OpenRecordset(strQuerySQL)

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 more information on SQL statements, search the Help index for "SQL statements."

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 dbs As Database, rstManagers As Recordset
Set dbs = CurrentDb
Set rstManagers = dbs.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 dbs As Database
Dim rstSalesReps As Recordset
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("SalesRepQuery")
qdf.SQL = "SELECT * FROM Employees WHERE Title = 'Sales Representative';"
Set rstSalesReps = dbs.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 dbs As Database
   Dim qdf As QueryDef
   Dim rstSalesReps As Recordset
   Set dbs = CurrentDb
   Set qdf = dbs.CreateQueryDef("SalesRepQuery")
   qdf.SQL = "SELECT * FROM Employees WHERE Title = 'Sales Representative'"
   Set rstSalesReps = qdf.OpenRecordset()
   ' Call the function to add a constraint.
   AddQueryFilter rstSalesReps
   ' Return database to original.
   dbs.QueryDefs.Delete "SalesRepQuery"
   rstSalesReps.Close
End Sub
Function AddQueryFilter(rst As Recordset)
   Dim qdf As QueryDef
   Dim strNewFilter As String, strRightSQL As String
   Set qdf = rst.CopyQueryDef
   ' Try "LastName LIKE 'D*'".
   strNewFilter = InputBox("Enter new criteria")
   strRightSQL = Right(qdf.SQL, 1)
   ' Strip characters from the end of the query,
   ' as needed.
   Do While strRightSQL = " " Or strRightSQL = ";" Or strRightSQL = vbCR Or _
      strRightSQL = vbLF
         qdf.SQL = Left(qdf.SQL, Len(qdf.SQL) - 1)
         strRightSQL = Right(qdf.SQL, 1)
   Loop
   qdf.SQL = qdf.SQL & " AND " & strNewFilter & ";"
   rst.Requery qdf         ' Requery the Recordset.
   rst.MoveLast            ' Populate the Recordset.
   ' "Lastname LIKE 'D*'" should return 2 records.
   MsgBox "Number returned = " & rst.RecordCount
End Function

Note   To use the Requery method, the Restartable property 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 a Microsoft 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 Restartable property. For more information on the Restartable property, search the Help index for "Restartable property."

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

See Also   For more information on the DAO Sort and Filter properties, search the Help index for the name of the property.

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

Function UpdateEmployees()
   Dim dbs As Database, rstEmployees As Recordset, strQuery As String
   Dim intI As Integer
   On Error GoTo ErrorHandler
   Set dbs = CurrentDb
   ' Open a recordset on all records from the Employees table that have
   ' a Null value in the ReportsTo field.
   strQuery = "SELECT * FROM Employees WHERE ReportsTo IS NULL;"
   Set rstEmployees = dbs.OpenRecordset(strQuery, dbOpenDynaset)
   ' If the recordset is empty, exit.
   If rstEmployees.EOF Then Exit Function
   intI = 1
   With rstEmployees
      Do Until .EOF
         .Edit
         ![ReportsTo] = 5
         ![Title] = "Temporary"
         ![Notes] = rstEmployees![Notes] & "Temp #" & intI
         .Update
         .MoveNext
         intI = intI + 1
      Loop
      .Close
   End With
   
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
      MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
End Function

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.

See Also   For more information on the Move methods, search the Help index for "Move."

Detecting the Limits of a Recordset Object

In a Recordset object, if you try to move too far in one direction, a run-time error occurs. For example, if you try to use the MoveNext method when you're already beyond the end 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 dbs As Database, rstOrders As Recordset
Set dbs = CurrentDb
Set rstOrders = dbs.OpenRecordset("Orders", dbOpenTable)
Do Until rstOrders.EOF
   .
   . ' Manipulate 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 data.
   .
   ' Move to the previous record.
   rstOrders.MovePrevious
Loop
rstOrders.Close               ' Close the Recordset.

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.

See Also   For more information on the BOF and EOF properties, search the Help index for "BOF property" and "EOF property."

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 RecCount(strSQL As String) As Long
   Dim rstCount As Recordset
   Dim dbs As Database
   On Error GoTo ErrorHandler
   Set dbs = CurrentDb
   Set rstCount = dbs.OpenRecordset(strSQL)
   If rstCount.EOF Then
      rstCount.Close
      RecCount = 0
      Exit Function
   Else
      rstCount.MoveLast
      RecCount = rstCount.RecordCount
      rstCount.Close
      Exit Function
   End If
         
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
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 on 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.

Important   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 on the PercentPosition property, search the Help index 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.

Function PercentPos()
   Dim dbs As Database, strMsg As String, rstEmployees As Recordset, intRet As Integer
   Dim intCount As Integer, strQuery As String, sngPercent As Single
   Dim varReturn As Variant
   Dim lngEmpID() As Long
   On Error GoTo ErrorHandler
   strQuery = "SELECT * FROM Employees;"
   Set dbs = CurrentDb
   Set rstEmployees = dbs.OpenRecordset(strQuery, dbOpenDynaset)
   With rstEmployees
      If .EOF Then            ' If no records, exit.
         Exit Function
      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
      .Close
   End With
   intRet = SysCmd(acSysCmdRemoveMeter)
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         ' Clear progress meter.
         varReturn = SysCmd(acSysCmdSetStatus, " ")
            Exit Function
   End Select
End Function

See Also   For more information on 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 methodsMoveFirst, MoveLast, MoveNext, MovePrevious, and Moveto 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.

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

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 intGetHireDate(lngEmpID As Long, varHireDate As Variant) As Integer
   Dim rstEmployees As Recordset, dbs As Database
   Const conFilePath As String = "C:\Program Files\Microsoft Office\Office\Samples\"
   On Error GoTo ErrorHandler
   Set dbs = OpenDatabase(conFilePath & "Northwind")
   Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenTable)
   rstEmployees.Index = "PrimaryKey"      ' The index name for Employee ID.
   rstEmployees.Seek "=", lngEmpID
   If rstEmployees.NoMatch Then
      varHireDate = Null
      ' The constants conErrNoMatch, conSuccess, and conFailed are defined at
      ' the module level as public constants with Integer values of
      ' -32,761, 0, and -32,737 respectively.
      intGetHireDate = conErrNoMatch
      Exit Function
   Else
      varHireDate = rstEmployees!HireDate
      intGetHireDate = conSuccess
      Exit Function
   End If
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         varHireDate = Null
         intGetHireDate = conFailed
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
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.

See Also   For more information on the NoMatch property, search the Help index for "NoMatch property."

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 dbs As Database, rstOrderDetail As Recordset
   On Error GoTo ErrorHandler
   Set dbs = CurrentDb
   Set rstOrderDetail = dbs.OpenRecordset("Order Details", dbOpenTable)
   rstOrderDetail.Index = "PrimaryKey"
   rstOrderDetail.Seek "=", lngOrderID, lngProductID
   If rstOrderDetail.NoMatch Then
      GetFirstPrice = Null
      MsgBox "Couldn't find order detail record."
   Else
      GetFirstPrice = rstOrderDetail!UnitPrice
   End If
   rstOrderDetail.Close
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
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 on 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 and adds the value in the OrderID field to the array lngOrderID().

Function FindEx(lngOrderID() As Long)
   Dim dbs As Database, rstOrders As Recordset
   Dim strQuery As String, rstOrderDetails As Recordset
   Dim intIndex As Integer
   On Error GoTo ErrorHandler
   Set dbs = CurrentDb
   ' Open recordsets on the Orders and Order Details tables. If there are no
   ' records in either table, exit the function.
   strQuery = "SELECT * FROM Orders ORDER BY OrderID;"
   Set rstOrders = dbs.OpenRecordset(strQuery, dbOpenSnapshot)
   If rstOrders.EOF Then Exit Function
   strQuery = "SELECT * FROM [Order Details] ORDER BY OrderID;"
   Set rstOrderDetails = dbs.OpenRecordset(strQuery, 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.
   rstOrderDetails.FindFirst "OrderID = " & rstOrders![OrderID]
   If rstOrderDetails.NoMatch Then
      ReDim Preserve lngOrderID(1 To intIndex)
      lngOrderID(intIndex) = rstOrders![OrderID]
   End If
   ' The first match has already been found, so use the FindNext method to find the
   ' next record satisfying the criteria.
   intIndex = 0
   Do Until rstOrders.EOF
      rstOrderDetails.FindNext "OrderID = " & rstOrders![OrderID]
      If rstOrderDetails.NoMatch Then
         intIndex = intIndex + 1
         ReDim Preserve lngOrderID(1 To intIndex)
         lngOrderID(intIndex) = rstOrders![OrderID]
      End If
      rstOrders.MoveNext
   Loop
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
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 use a bookmark to save the current record position. You can then perform other operations on the Recordset object, and then return to the saved record position.

Function BookMarkEx() As Integer
   Dim dbs As Database, rstProducts As Recordset
   Dim vBookMark As Variant, sngRevenue As Single
   Dim strQuery As String, rstCategories As Recordset, strCriteria As String
   On Error GoTo ErrHandler
   BookMarkEx = 0
   strQuery = "SELECT * FROM Products WHERE UnitsOnOrder >= 40 ORDER BY " _
       & "CategoryID, UnitsOnOrder DESC;"
   Set dbs = CurrentDb
   Set rstProducts = dbs.OpenRecordset(strQuery, dbOpenSnapshot)
   Set rstCategories = dbs.OpenRecordset("SELECT CategoryID FROM " _
       & "Categories ORDER BY CategoryID;", dbOpenSnapshot)
   If rstProducts.NoMatch Then Exit Function
   ' 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
      sngRevenue = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]
      If Not rstProducts.NoMatch Then
         ' Set a bookmark at the first record containing the CategoryID.
         vBookMark = rstProducts.Bookmark
         ' Find the product generating the most revenue.
         Do While rstProducts![CategoryID] = rstCategories![CategoryID]
            If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] > sngRevenue Then
               sngRevenue = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]
            End If
            rstProducts.MoveNext
         Loop
         ' Move to the first record containing the CategoryID.
         rstProducts.Bookmark = vBookMark
         sngRevenue = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]
         ' Find the product generating the least revenue.
         Do While rstProducts![CategoryID] = rstCategories![CategoryID]
            If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] < sngRevenue Then
               sngRevenue = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]
            End If
            rstProducts.MoveNext
         Loop
      End If
      rstCategories.MoveNext
   Loop
' Error Handler.
ErrHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
End Function

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 on 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 dbs As Database
Dim rstOriginal As Recordset, rstDuplicate As Recordset
Dim strPlaceholder As String
Set dbs = CurrentDb
' Create the first Recordset.
Set rstOriginal = dbs.OpenRecordset("Orders", dbOpenDynaset)
' Save the current record position.
strPlaceholder = rstOriginal.Bookmark
' Create a duplicate Recordset.
Set rstDuplicate = rstOriginal.Clone()
' Go to same record.
rstDuplicate.Bookmark = strPlaceholder
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 Recordset
   Dim strOrigin As String
   Dim strEmployee As String
   Dim strSuper As String
   Set frmEmployees = Screen.ActiveForm
   ' Open the Recordset.
   Set rstEmployees = frmEmployees.RecordsetClone
   strOrigin = 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 = strOrigin
   End If
   rstEmployees.Close
End Sub

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

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

See Also   For more information on the Bookmarkable property, search the Help index for "Bookmarkable property."

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 a 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, _
   lngOrderIDs() As Long)
   Dim dbs As Database, rstOrders As Recordset
   Dim qdf As QueryDef, strSQL As String, intI As Integer
   On Error GoTo ErrorHandler
   Set dbs = 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 qdf = dbs.CreateQueryDef("", strSQL)
   ' Set the query parameters.
   qdf.Parameters("DateFrom") = strDateFrom
   qdf.Parameters("DateTo") = strDateTo
   ' Open a forward-only snapshot on the query.
   Set rstOrders = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)
   ' Load all the OrderIDs in the query into an array that the caller
   ' of the function can use.
   intI = 1
   While rstOrders.EOF = False
      ReDim lngOrderIDs(1 To intI)
      lngOrderIDs(intI) = rstOrders!OrderID
      intI = intI + 1
      rstOrders.MoveNext
   Wend
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
End Function

See Also   For more information on parameter queries, search the Help index for "parameter queries."

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. The following example creates a QueryDef object to update the Employees table and then runs the query:

Dim dbs As Database, qdfChangeTitles As QueryDef
Set dbs = CurrentDb
Set qdfChangeTitles = dbs.CreateQueryDef("")
qdfChangeTitles.SQL = "UPDATE Employees SET Title = 'Account Executive' " _
   & "WHERE Title = 'Sales Representative';"
qdfChangeTitles.Execute dbFailOnError      ' Invoke query.

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 dbs As Database, qdfChangeTitles As QueryDef
Dim strSQLUpdate As String, strOld As String
Dim strNew As String
Set dbs = CurrentDb
strSQLUpdate = "PARAMETERS [Old Title] Text, [New Title] Text; " _
   & "UPDATE Employees SET Title = [New Title] WHERE Title = [Old Title];"
' Create the QueryDef object.
Set qdfChangeTitles = dbs.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 more efficient than code that loops through records looking for records to delete, especially with databases created in Microsoft Access for Windows 95 or later.

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

1   Go to the record that you want to change.

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

3   Make the necessary changes to the record.

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

The following example illustrates how to change the job titles for all sales representatives in a table called Employees:

Dim dbs As Database, rstEmployees As Recordset
Set dbs = CurrentDb
Set rstEmployees = dbs.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
rstEmployees.Close

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

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:

Function DeleteDuplicateShippers() As Integer
   Dim rstShippers As Recordset, strQuery As String, dbs As Database, strName As String
   On Error GoTo ErrorHandler
   strQuery = "SELECT * FROM Shippers ORDER BY CompanyName;"
   Set dbs = CurrentDb
   Set rstShippers = dbs.OpenRecordset(strQuery, dbOpenDynaset)
   ' If no records in Shippers table, exit.
   If rstShippers.EOF Then Exit Function
   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
   
ErrorHandler:
   Select Case Err
      Case 0
         ' The constants conSuccess and conFailed are defined at
         ' the module level as public constants with Integer values of
         ' 0 and -32,737 respectively.
         DeleteDuplicateShippers = conSuccess
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         DeleteDuplicateShippers = conFailed
         Exit Function
   End Select
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

1   Use the AddNew method to create a new record you can edit.

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

3   Use the Update method to save the new record.

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

Dim dbs As Database, rstShippers As Recordset
Set dbs = CurrentDb
Set rstShippers = dbs.OpenRecordset("Shippers")
rstShippers.AddNew
rstShippers!CompanyName = "Global Parcel Service"
   .
   . ' Set remaining fields.
   .
rstShippers.Update
rstShippers.Close

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.

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

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 Microsoft 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 on client/server architecture, see Chapter 19, "Developing Client/Server Applications."

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.

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 dbsSales As Database
   Dim rstEmp As Recordset
   Dim wrkCurrent As Workspace
   Set wrkCurrent = DBEngine.Workspaces(0)
   Set dbsSales = OpenDatabase("Northwind.mdb")
   Set rstEmp = dbsSales.OpenRecordset("Employees", dbOpenTable)
   wrkCurrent.BeginTrans
   Do Until rstEmp.EOF
      If rstEmp!Title = "Sales Representative" Then
         rstEmp.Edit
         rstEmp!Title = "Sales Associate"
         rstEmp.Update
      End If
      rstEmp.MoveNext
   Loop
   If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
      wrkCurrent.CommitTrans
   Else
      wrkCurrent.Rollback
   End If
   rstEmp.Close
   dbsSales.Close
End Sub

When you use transactions, all databases and Recordset objects in the specified Workspace object are affectedtransactions 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 and Microsoft Access 97 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 Microsoft 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 Windows Registry.

See Also   For information on tuning Registry settings used by Microsoft Jet, see "Adjusting Windows Registry Settings to Improve Performance" in Chapter 13, "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 dbs As Database, rstEmployees As Recordset
Dim strFirstName As String, strLastName As String
Dim strTitle As String

Set dbs = CurrentDb
Set rstEmployees = dbs.OpenRecordset("Employees")
rstEmployees.MoveFirst
strFirstName = rstEmployees!FirstName
strLastName = rstEmployees!LastName
strTitle = rstEmployees!Title
rstEmployees.Close

See Also   For more information on 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 dbs As Database
   Dim rstEmployees As Recordset
   Dim varRecords As Variant
   Dim intNumReturned As Integer
   Dim intNumColumns As Integer
   Dim intColumn As Integer, intRow As Integer

   Set dbs = CurrentDb
   Set rstEmployees = dbs.OpenRecordset("SELECT FirstName, LastName, Title " _
      & "FROM Employees", 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
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 problemand 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 on OLE Object and Memo fields, see "The OLE Object and Memo Data Types" later in this chapter.

© 1996 Microsoft Corporation. All rights reserved.