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:
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.
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.
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.
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.
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:
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.
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:
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.
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.
Set rstNew = qdfQueryData.OpenRecordset
Only dynaset- and snapshot-type Recordset objects are available, and the dynaset type is the default.
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.
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:
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:
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.”
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.
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.
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.”
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.
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.
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.”
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.
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.
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.”
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.
See Also For more information on the DAO Sort and Filter properties, search the Help index for the name of the property.
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:
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.”
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:
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.”
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.
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.
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.
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.
| 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 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.
You can use the Find methods to locate a record in a dynaset- or snapshot-type Recordset object. DAO provides four Find methods:
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.
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.
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
If you have used another database or programming environment, you may be accustomed to referring to record numbers. For example, you may have written code that opens a text file and thereafter refers to specific records by their relative position in the file. The first record in the file would be record 1, the second would be record 2, and so on.
In Microsoft 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.
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.”
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.
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.”
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.
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
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.
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 updatable. For example, you can’t change the value of an AutoNumber field, and a Recordset based on certain linked tables may not be updatable.
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.
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
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.
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.
See Also For more information on caching ODBC data, see Chapter 19, “Developing Client/Server Applications.”
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.
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.”
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 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 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).
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 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.
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.
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 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.
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.”
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.
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.
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 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 on OLE Object and Memo fields, see “The OLE Object and Memo Data Types” later in this chapter.