| Microsoft Access 2000: Building Applications with Forms and Reports |
CHAPTER 6
The Microsoft® Jet database engine supports a rich set of Data Access Objects (DAO) features for organizing, sorting, searching, updating, adding, and deleting data. The Recordset object alone provides 24 methods and 34 properties that give you a great deal of control over records in a database. With the Recordset object's Fields collection and the properties and methods of the Field object, you can manipulate data at the field level. This chapter describes how to manipulate records and fields by using the DAO Recordset and Field objects.
For information about manipulating records and fields using ActiveX® Data Objects (ADO) with the Jet database engine, see Chapter 14, "Working with the Data Access Components of an Office Solution," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Chapter Contents
Using Recordset Objects
Using Field Objects
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 about 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 = dbsDatabase.OpenRecordset("Data Source")
If Data Source is a table local to the database, all four types are available, and the table-type Recordset object is the default. If Data Source is anything else, only dynaset- and snapshot-type Recordset objects are available, and the dynaset type is the default.
Set rstNew = tdfTableData.OpenRecordset
If tdfTableData refers to a table in an Access database (.mdb) or to an installable ISAM database opened directly, then all four types are available and the table-type Recordset object is the default type. If tdfTableData is in an ODBC database or is a linked table in an external database, only dynaset- and snapshot-type Recordset objects are available, and the dynaset type is the default.
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 about inconsistent updates, see "Inconsistent Updates" later in this chapter.
With the lockedits argument of the OpenRecordset method, you can control how locking is handled for a Recordset object. You can use the following constants:
The default value is dbPessimistic. The only effect of using dbPessimistic or dbOptimistic is to preset the value of the Recordset object's LockEdits property.
Note Setting both the lockedits argument and the options argument to dbReadOnly generates a run-time error.
See Also For more information about optimistic and pessimistic locking, see Chapter 16, "Multiuser Database Solutions," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). For more information about using Recordset objects in client/server applications, see Chapter 14, "Working with the Data Access Components of an Office Solution," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
You can create a Recordset object based on an 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 an Access form. The following example shows how to assign a Recordset object to the records in the Orders form:
Dim rstOrders As DAO.Recordset
Set rstOrders = Forms!Orders.RecordsetClone
This code always creates the type of Recordset being cloned (the type of Recordset on which the form is based); no other types are available. Note that the Recordset object is declared with the object library qualification. Since Access can now use both DAO and ADO, it is better to fully qualify the data access variables by including the object library reference name.
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 dbsNorthwind As DAO.Database
Dim rstCustomers As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstCustomers = dbsNorthwind.OpenRecordset("Customers")
Notice that you don't need to use the dbOpenTable constant to create a table-type Recordset. If you omit the type constant, as discussed in "Default Recordset Types" earlier in this chapter, DAO chooses the highest-functionality Recordset type available, depending on the object in which the Recordset is created, and the data source. Because the table type is available when you open a Recordset from a local table, DAO uses it.
The following example creates a dynaset-type Recordset object for a linked Paradox® version 3.x table. Because the table type isn't available when you open a Recordset from a linked table in a database other than an Access database, DAO selects the next most efficient type, opening a dynaset-type Recordset.
Dim dbsPublish As DAO.Database
Dim tdfAuthor As DAO.TableDef
Dim rstAuthor As DAO.Recordset
'Get current database.
Set dbsPublish = CurrentDb
Set tdfAuthor = dbsPublish.CreateTableDef("PDXAuthor")
'Connect to the Paradox table Author in the database
'C:\PDX\Publish.
tdfAuthor.Connect = "Paradox 3.X;DATABASE=C:\PDX\Publish"
tdfAuthor.SourceTableName = "Author"
'Link the table.
dbsPublish.TableDefs.Append tdfAuthor
'Create a dynaset-type Recordset for the table.
Set rstAuthor = tdfAuthor.OpenRecordset()
You can also open a Paradox table directly by first opening the Paradox database.
You can order records in a table-type Recordset object by setting its Index property. Any Index object in the Indexes collection of the Recordset object's underlying table definition can be specified with the Index property.
The following example creates a table-type Recordset object based on the Customers table, by using an existing index called City:
Dim dbsNorthwind As DAO.Database
Dim rstCustomers As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstCustomers = dbsNorthwind.OpenRecordset("Customers")
'Move to the first record.
rstCustomers.MoveFirst
'First record with no index set.
MsgBox rstCustomers!CompanyName
rstCustomers.Index = "City" 'Select the City index.
rstCustomers.MoveFirst 'Move to the first record.
MsgBox rstCustomers!CompanyName
rstCustomers.Close
dbsNorthwind.Close
Set rstCustomers = Nothing
Set dbsNorthwind = Nothing
If you set the Index property to an index that doesn't exist, a trappable run-time error occurs. If you want to sort records according to an index that doesn't exist, either create the index first, or create a dynaset- or snapshot-type Recordset by using a query that returns records in a specified order.
Note You must set the Index property before using the Seek method. For information about using the Seek method to locate records that satisfy criteria that you specify, see "Finding a Record in a Table-Type Recordset Object" later in this chapter.
You can also create a Recordset object based on a stored select query. In the following example, Current Product List is an existing select query stored in the current database:
Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstProducts = dbsNorthwind.OpenRecordset("Current Product List")
If a stored select query doesn't already exist, the OpenRecordset method also accepts an SQL string instead of the name of a query. The previous example can be rewritten as follows:
Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Dim strSQL As String
Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Products WHERE Discontinued = No " & _
"ORDER BY ProductName"
Set rstProducts = dbsNorthwind.OpenRecordset(strSQL)
The disadvantage of this approach is that the query string must be compiled each time it's run, whereas the stored query is compiled the first time it's saved, which usually results in slightly better performance.
Note When you create a Recordset object by using an SQL string or a stored query, your code doesn't continue running until the query returns the first row in the Recordset.
See Also For information about SQL statements, type SQL in the Office Assistant or on the Answer Wizard tab in the Help window and then click Search, or read the "Microsoft Jet SQL Reference" found in Microsoft Access Help (Help menu). For more information about SQL statements, see "Writing SQL Statements" in Chapter 15, "Retrieving and Analyzing Data," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Unless you open a table-type Recordset object and set its Index property, you can't be sure records will appear in any specific order. However, you usually want to retrieve records in a specific order. For example, you may want to view invoices arranged by increasing invoice number, or retrieve employee records in alphabetic order by their last names. To see records in a specific order, sort them.
To sort data in a Recordset object that isn't a table, use an SQL ORDER BY clause in the query that constructs the Recordset. You can specify an SQL string when you create a QueryDef object, when you create a stored query in a database, or when you use the OpenRecordset method.
You can also filter data, which means you restrict the result set returned by a query to records that meet some criteria. With any type of Recordset object, use an SQL WHERE clause in the original query to filter data.
The following example opens a dynaset-type Recordset object, and uses an SQL statement to retrieve, filter, and sort records:
Dim dbsNorthwind As DAO.Database
Dim rstManagers As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstManagers = dbsNorthwind.OpenRecordset("SELECT FirstName, " & _
"LastName FROM Employees WHERE Title = " & _
"'Sales Manager' ORDER BY LastName")
One drawback of running an SQL query in an OpenRecordset method is that it has to be recompiled every time you run it. If this query is used frequently, you can improve performance by first creating a stored query using the same SQL statement, and then opening a Recordset object against the query, as shown in the following example:
Dim dbsNorthwind As DAO.Database
Dim rstSalesReps As DAO.Recordset
Dim qdfSalesReps As DAO.QueryDef
Set dbsNorthwind = CurrentDb
Set qdfSalesReps = dbsNorthwind.CreateQueryDef("SalesRepQuery")
qdfSalesReps.SQL = "SELECT * FROM Employees WHERE Title = " & _
"'Sales Representative'"
Set rstSalesReps = dbsNorthwind.OpenRecordset("SalesRepQuery")
Note For even greater flexibility and control at run time, you can use query parameters to determine the sort order and filter criteria. For more information, see "Using Parameter Queries" later in this chapter.
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 dbsNorthwind As DAO.Database
Dim qdfSalesReps As DAO.QueryDef
Dim rstSalesReps As DAO.Recordset
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
Set qdfSalesReps = dbsNorthwind.CreateQueryDef("SalesRepQuery")
qdfSalesReps.SQL = "SELECT * FROM Employees WHERE Title = " & _
"'Sales Representative'"
Set rstSalesReps = qdfSalesReps.OpenRecordset()
'Call the function to add a constraint.
AddQueryFilter rstSalesReps
'Return database to original.
dbsNorthwind.QueryDefs.Delete "SalesRepQuery"
rstSalesReps.Close
qdfSalesReps.Close
dbsNorthwind.Close
Set rstSalesReps = Nothing
Set qdfSalesReps = Nothing
Set dbsNorthwind = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
Sub AddQueryFilter(rstData As Recordset)
Dim qdfData As DAO.QueryDef
Dim strNewFilter As String
Dim strRightSQL As String
On Error GoTo ErrorHandler
Set qdfData = rstData.CopyQueryDef
'Try "LastName LIKE 'D*'".
strNewFilter = InputBox("Enter new criteria")
strRightSQL = Right(qdfData.SQL, 1)
'Strip characters from the end of the query,
'as needed.
Do While strRightSQL = " " Or strRightSQL = ";" Or _
strRightSQL = vbCR Or strRightSQL = vbLF
qdfData.SQL = Left(qdfData.SQL, Len(qdfData.SQL) - 1)
strRightSQL = Right(qdfData.SQL, 1)
Loop
qdfData.SQL = qdfData.SQL & " AND " & strNewFilter
rstData.Requery qdfData 'Requery the Recordset.
rstData.MoveLast 'Populate the Recordset.
'"Lastname LIKE 'D*'" should return 2 records.
MsgBox "Number of records found: " & rstData.RecordCount & "."
qdfData.Close
Set qdfData = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
Note To use the Requery method, 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 an Access database. You can't restart SQL pass-through queries. You may or may not be able to restart queries against linked tables in another database format. To determine whether a Recordset object can rerun its query, check the 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.
A Recordset object usually has a current position, most often at a record. When you refer to the fields in a Recordset, you obtain values from the record at the current position, which is known as the current record. However, the current position can also be immediately before the first record in a Recordset or immediately after the last record. In certain circumstances, the current position is undefined.
See Also For more information about referring to fields, see "Referring to Field Objects" later in this chapter.
You can use the following Move methods to loop through the records in a Recordset:
You can use each of these methods on table-, dynaset-, and snapshot-type Recordset objects. On a forward-only-type Recordset object, you can use only the MoveNext and Move methods. If you use the Move method on a forward-only-type Recordset, the argument specifying the number of rows to move must be a positive integer.
The following example opens a Recordset object on the Employees table containing all of the records that have a Null value in the ReportsTo field. The function then updates the records to indicate that these employees are temporary employees. For each record in the Recordset, the example changes the Title and Notes fields, and saves the changes with the Update method. It uses the MoveNext method to move to the next record.
Sub UpdateEmployees()
Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strSQL As String
Dim intI As Integer
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
'Open a recordset on all records from the Employees table that have
'a Null value in the ReportsTo field.
strSQL = "SELECT * FROM Employees WHERE ReportsTo IS NULL"
Set rstEmployees = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)
'If the recordset is empty, exit.
If rstEmployees.EOF Then Exit Sub
intI = 1
With rstEmployees
Do Until .EOF
.Edit
![ReportsTo] = 5
![Title] = "Temporary"
![Notes] = rstEmployees![Notes] & "Temp #" & intI
.Update
.MoveNext
intI = intI + 1
Loop
End With
RstEmployees.Close
dbsNorthwind.Close
Set rstEmployees = Nothing
Set dbsNorthwind = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
Note The previous example is provided only for the purposes of illustrating the Update and MoveNext methods. It would be much faster to perform this bulk operation with an SQL UPDATE query.
In a Recordset object, if you try to move beyond the beginning or ending record, a run-time error occurs. For example, if you try to use the MoveNext method when you're already at the last record of the Recordset, a trappable error occurs. For this reason, it's helpful to know the limits of the Recordset object.
The BOF property indicates whether the current position is at the beginning of the Recordset. If BOF is True, the current position is before the first record in the Recordset. The BOF property is also True if there are no records in the Recordset when it's opened. Similarly, the EOF property is True if the current position is after the last record in the Recordset, or if there are no records.
The following example shows you how to use the BOF and EOF properties to detect the beginning and end of a Recordset object. This code fragment creates a table-type Recordset based on the Orders table from the current database. It moves through the records, first from the beginning of the Recordset to the end, and then from the end of the Recordset to the beginning.
Dim dbsNorthwind As DAO.Database
Dim rstOrders As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstOrders = dbsNorthwind.OpenRecordset("Orders")
'Do until ending of file.
Do Until rstOrders.EOF
'
' Manipulate the data.
'
rstOrders.MoveNext ' Move to the next record.
Loop
rstOrders.MoveLast ' Move to the last record.
'Do until beginning of file.
Do Until rstOrders.BOF
'
' Manipulate the data.
'
rstOrders.MovePrevious ' Move to the previous record.
Loop
Notice that there's no current record immediately following the first loop. The BOF and EOF properties both have the following characteristics:
The following illustration shows the settings of the BOF and EOF properties for all possible current positions in a Recordset.
You may want to know the number of records in a Recordset object. For example, you may want to create a form that shows how many records are in each of the tables in a database. Or you may want to change the appearance of a form or report based on the number of records it includes.
The RecordCount property contains the number of records in a table-type Recordset or the total number of records accessed in a dynaset- or snapshot-type Recordset. A Recordset object with no records has a RecordCount property value of 0.
Note The value of the RecordCount property equals the number of records that have actually been accessed. For example, when you first create a dynaset or snapshot, you have accessed (or visited) only one record. If you check the RecordCount property immediately after creating the dynaset or snapshot (assuming it has at least one record), the value is 1. To visit all the records, use the MoveLast method immediately after opening the Recordset, then use MoveFirst to return to the first record. This isn't done automatically because it may be slow, especially for large result sets.
When you open a table-type Recordset object, you effectively visit all of the records in the underlying table, and the value of the RecordCount property totals the number of records in the table as soon as the Recordset is opened. Canceled transactions may make the value of the RecordCount property out-of-date in some multiuser situations. Compacting the database restores the table's record count to the correct value.
The following example creates a snapshot-type Recordset object, and then determines the number of records in the Recordset:
Function FindRecordCount(strSQL As String) As Long
Dim dbsNorthwind As DAO.Database
Dim rstRecords As DAO.Recordset
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
Set rstRecords = dbsNorthwind.OpenRecordset(strSQL)
If rstRecords.EOF Then
FindRecordCount = 0
Else
rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If
rstRecords.Close
dbsNorthwind.Close
Set rstRecords = Nothing
Set dbsNorthwind = Nothing
Exit Function
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function
As your application deletes records in a dynaset-type Recordset, the value of the RecordCount property decreases. However, in a multiuser environment, records deleted by other users aren't reflected in the value of the RecordCount property until the current record is positioned on a deleted record. At that time, the setting of the RecordCount property decreases by one. Using the Requery method on a Recordset, followed by the MoveLast method, sets the RecordCount property to the current total number of records in the Recordset.
A snapshot-type Recordset object is static and the value of its RecordCount property doesn't change when you add or delete records in the snapshot's underlying table.
In some situations, you need to determine how far through a Recordset object you have moved the current record position, and perhaps indicate the current record position to a user. For example, you may want to indicate the current position on a dial, meter, or similar type of control. Two properties are available to indicate the current position: the AbsolutePosition property and the PercentPosition property.
The AbsolutePosition property value is the position of the current record relative to 0. However, don't think of this property as a record number; if the current record is undefined, the AbsolutePosition property returns – 1. In addition, there is no assurance that a record will have the same absolute position if the Recordset object is re-created because the order of individual records within a Recordset object isn't guaranteed unless it's created with an SQL statement that includes an ORDER BY clause.
See Also For more information about the AbsolutePosition property, see "Why Use Bookmarks Instead of Record Numbers?" later in this chapter.
The PercentPosition property shows the current position expressed as a percentage of the total number of records indicated by the RecordCount property. Because the RecordCount property doesn't reflect the total number of records in the Recordset object until the Recordset has been fully populated, the PercentPosition property only reflects the current record position as a percentage of the number of records that have been accessed since the Recordset was opened. To make sure that the PercentPosition property reflects the current record position relative to the entire Recordset, use the MoveLast and MoveFirst methods immediately after opening the Recordset. This fully populates the Recordset object before you use the PercentPosition property. If you have a large result set, using the MoveLast method may take a long time for Recordsets that aren't of type table.
Note The PercentPosition property is only an approximation and shouldn't be used as a critical parameter. This property is best suited for driving an indicator that marks a user's progress while moving though a set of records. For example, you may want a control that indicates the percent of records completed. For more information about the PercentPosition property, search Microsoft Access Help (Help menu) for "PercentPosition property."
The following example opens a Recordset object on a table called Employees. The procedure then moves through the Employees table and uses the SysCmd function to display a progress bar showing the percentage of the table that's been processed. If the hire date of the employee is before Jan. 1, 1993, the text "Senior Staff" is appended to the Notes field.
Sub AddEmployeeNotes()
Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strMsg As String
Dim intRet As Integer
Dim intCount As Integer
Dim strSQL As String
Dim sngPercent As Single
Dim varReturn As Variant
Dim lngEmpID() As Long
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Employees"
Set rstEmployees = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)
With rstEmployees
If .EOF Then ' If no records, exit.
Exit Sub
Else
strMsg = "Processing Employees table..."
intRet = SysCmd(acSysCmdInitMeter, strMsg, 100)
End If
Do Until .EOF
If !HireDate < #1/1/93# Then
.Edit
!Notes = !Notes & ";" & "Senior Staff"
.Update
End If
If .PercentPosition <> 0 Then
intRet = SysCmd(acSysCmdUpdateMeter, .PercentPosition)
End If
.MoveNext
Loop
End With
intRet = SysCmd(acSysCmdRemoveMeter)
rstEmployees.Close
dbsNorthwind.Close
Set rstEmployees = Nothing
Set dbsNorthwind = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
varReturn = SysCmd(acSysCmdSetStatus, " ")
End Sub
See Also For more information about reading data in a field, see "Reading and Writing Data" later in this chapter.
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.
Note If you use the Seek method on a table-type Recordset object without first setting the current index, a run-time error occurs.
The syntax for the Seek method is:
table.Seek comparison, key1, key2 ...
The table argument is the table-type Recordset object you're searching through. The comparison argument is a string that determines the kind of comparison that is being performed. The following table lists the comparison strings you can use with the Seek method.
| Comparison string | Description |
| "=" | Equal to the specified key values |
| ">=" | Greater than or equal to the specified key values |
| ">" | Greater than the specified key values |
| "<=" | Less than or equal to the specified key values |
| "<" | Less than the specified key values |
The keyn arguments are a series of one or more values that correspond to the field or fields that make up the current index of the Recordset. Microsoft Jet compares these values to values in the corresponding fields of the Recordset object's records.
The following example opens a table-type Recordset object called Employees, and uses the Seek method to locate the record containing a value of lngEmpID in the EmployeeID field. It returns the hire date for the specified employee.
Function GetHireDate(lngEmpID As Long) As Variant
Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDB
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
'The index name for Employee ID.
rstEmployees.Index = "PrimaryKey"
rstEmployees.Seek "=", lngEmpID
If rstEmployees.NoMatch Then
GetHireDate = Null
Else
GetHireDate = rstEmployees!HireDate
End If
rstEmployees.Close
dbsNorthwind.Close
Set rstEmployees = Nothing
Set dbsNorthwind = Nothing
Exit Function
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function
The Seek method always starts searching for records at the beginning of the Recordset object. If you use the Seek method with the same arguments more than once on the same Recordset, it finds the same record.
You can use the NoMatch property on the Recordset object to test whether a record matching the search criteria was found. If the record matching the criteria was found, the NoMatch property will be False; otherwise it will be True.
The following example illustrates how you can create a function that uses the Seek method to locate a record by using a multiple-field index:
Function GetFirstPrice(lngOrderID As Long, lngProductID As Long) As Variant
Dim dbsNorthwind As DAO.Database
Dim rstOrderDetail As DAO.Recordset
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
Set rstOrderDetail = dbsNorthwind.OpenRecordset("Order Details")
rstOrderDetail.Index = "PrimaryKey"
rstOrderDetail.Seek "=", lngOrderID, lngProductID
If rstOrderDetail.NoMatch Then
GetFirstPrice = Null
Else
GetFirstPrice = rstOrderDetail!UnitPrice
End If
rstOrderDetail.Close
dbsNorthwind.Close
Set rstOrderDetail = Nothing
Set dbsNorthwind = Nothing
Exit Function
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function
In this example, the table's primary key consists of two fields: OrderID and ProductID. When you call the GetFirstPrice function with a valid (existing) combination of OrderID and ProductID field values, the function returns the unit price from the found record. If it can't find the combination of field values you want in the table, the function returns the Null value.
If the current index is a multiple-field index, trailing key values can be omitted and are treated as Null values. That is, you can leave off any number of key values from the end of a Seek method's key argument, but not from the beginning or the middle. However, if you don't specify all values in the index, you can use only the ">" or "<" comparison string with the Seek method.
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 about bookmarks, see the following section "Marking Record Position with Bookmarks."
The NoMatch property is False whenever the operation succeeds. In this case, the current record position is the record found by one of the Find methods.
The following example illustrates how you can use the FindNext method to find all orders in the Orders table that have no corresponding records in the Order Details table. The function searches for missing orders and, if it finds one, it adds the value in the OrderID field to the array aryOrders().
Function FindOrders() As Variant
Dim dbsNorthwind As DAO.Database
Dim rstOrders As DAO.Recordset
Dim rstOrderDetails As DAO.Recordset
Dim strSQL As String
Dim intIndex As Integer
Dim aryOrders() As Long
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
'Open recordsets on the Orders and Order Details tables. If there are
'no records in either table, exit the function.
strSQL = "SELECT * FROM Orders ORDER BY OrderID"
Set rstOrders = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)
If rstOrders.EOF Then Exit Function
strSQL = "SELECT * FROM [Order Details] ORDER BY OrderID"
Set rstOrderDetails = dbsNorthwind.OpenRecordset(strSQL, _
dbOpenSnapshot)
'For the first record in Orders, find the first matching record
'in OrderDetails. If no match, redimension the array of order IDs and
'add the order ID to the array.
intIndex = 1
rstOrderDetails.FindFirst "OrderID = " & rstOrders![OrderID]
If rstOrderDetails.NoMatch Then
ReDim Preserve aryOrders (1 To intIndex)
aryOrders (intIndex) = rstOrders![OrderID]
rstOrders.MoveNext
End If
'The first match has already been found, so use the FindNext method to
'find the next record satisfying the criteria.
Do Until rstOrders.EOF
rstOrderDetails.FindNext "OrderID = " & rstOrders![OrderID]
If rstOrderDetails.NoMatch Then
intIndex = intIndex + 1
ReDim Preserve aryOrders (1 To intIndex)
aryOrders (intIndex) = rstOrders![OrderID]
End If
rstOrders.MoveNext
Loop
FindOrders = aryOrders
rstOrders.Close
rstOrderDetails.Close
dbsNorthwind.Close
Set rstOrders = Nothing
Set rstOrderDetails = Nothing
Set dbsNorthwind = Nothing
Exit Function
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function
Tip If you need to frequently search records in a dynaset, you may find it easier to create a temporary indexed table and use the Seek method instead.
A bookmark is a system-generated Byte array that uniquely identifies each record. The DAO Bookmark property of a Recordset object changes each time you move to a new record. To identify a record, move to that record and then assign the value of the DAO Bookmark property to a variable of type Variant. To return to the record, set the DAO Bookmark property to the value of the variable.
The following example illustrates how you can find the product that makes the most and least revenue per category by using a bookmark to save the current record position. By using a bookmark, you can perform other operations on the Recordset object, and then return to the saved record position.
Sub GetProductStats()
Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Dim rstCategories As DAO.Recordset
Dim varFirstMark As Variant
Dim varHighMark As Variant
Dim varLowMark As Variant
Dim curHighRev As Currency
Dim curLowRev As Currency
Dim strSQL As String
Dim strCriteria As String
Dim strMessage As String
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Products WHERE UnitsOnOrder >= 40 " & _
"ORDER BY CategoryID, UnitsOnOrder DESC"
Set rstProducts = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)
If rstProducts.EOF Then Exit Sub
StrSQL = "SELECT CategoryID, CategoryName FROM Categories " & _
"ORDER BY CategoryID"
Set rstCategories = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)
'For each category find the product generating the least revenue
'and the product generating the most revenue.
Do Until rstCategories.EOF
strCriteria = "CategoryID = " & rstCategories![CategoryID]
rstProducts.FindFirst strCriteria
curHighRev = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]
If Not rstProducts.NoMatch Then
'Set bookmarks at the first record containing the CategoryID.
varFirstMark = rstProducts.Bookmark
varHighMark = varFirstMark
varLowMark = varFirstMark
'Find the product generating the most revenue.
Do While rstProducts![CategoryID] = rstCategories![CategoryID]
If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] > _
curHighRev Then
curHighRev = rstProducts![UnitPrice] * _
rstProducts![UnitsOnOrder]
varHighMark = rstProducts.Bookmark
End If
rstProducts.MoveNext
Loop
'Move to the first record containing the CategoryID.
rstProducts.Bookmark = varFirstMark
curLowRev = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]
' Find the product generating the least revenue.
Do While rstProducts![CategoryID] = rstCategories![CategoryID]
If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] < _
curLowRev Then
curLowRev = rstProducts![UnitPrice] * _
rstProducts![UnitsOnOrder]
varLowMark = rstProducts.Bookmark
End If
rstProducts.MoveNext
Loop
End If
'Set high & low bookmarks to build the message string.
strMessage = "CATEGORY: " & rstCategories!CategoryName & _
vbCrLf & vbCrLf
rstProducts.Bookmark = varHighMark
strMessage = strMessage & "HIGH: $" & curHighRev & " " & _
rstProducts!ProductName & vbCrLf
rstProducts.Bookmark = varLowMark
strMessage = strMessage & "LOW: $" & curLowRev & " " & _
rstProducts!ProductName
MsgBox strMessage, , "Product Statistics"
rstCategories.MoveNext
Loop
rstProducts.Close
rstCategories.Close
dbsNorthwind.Close
Set rstProducts = Nothing
Set rstCategories = Nothing
Set dbsNorthwind = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
A bookmark is particularly useful if a method fails because the current record position is undefined.
The LastModified property of the Recordset object provides a good illustration of how to use a bookmark. The LastModified property returns the bookmark of the last record in the Recordset to be added or modified. To use it, set the DAO Bookmark property equal to the LastModified property, as follows:
rstCustomers.Bookmark = rstCustomers.LastModified
This moves the current record position to the last record that was added or modified. This is particularly useful when adding new records, because after you add a new record, the current record is the one you were on before you added the record. With the LastModified property, you can move to the newly added record if that's what your application expects.
See Also For more information about adding new records, see "Adding a New Record" later in this chapter.
When you close a Recordset object, any bookmarks you saved become invalid. You can't use a bookmark from one Recordset in another Recordset, even if both Recordset objects are based on the same underlying table or query. However, you can use a bookmark on the clone of a Recordset, as shown in the following example:
Dim dbsNorthwind As DAO.Database
Dim rstOriginal As DAO.Recordset
Dim rstDuplicate As DAO.Recordset
Dim varBookMark As Variant
Set dbsNorthwind = CurrentDb
'Create the first Recordset.
Set rstOriginal = dbsNorthwind.OpenRecordset("Orders", dbOpenDynaset)
'Save the current record position.
varBookMark = rstOriginal.Bookmark
'Create a duplicate Recordset.
Set rstDuplicate = rstOriginal.Clone()
'Go to same record.
rstDuplicate.Bookmark = varBookMark
rstOriginal.Close
You can also use the DAO Bookmark property on the Recordset object underlying a form. With this property, your code can mark which record is currently displayed on the form, and then change the record that is being displayed. For example, on a form containing employee information, you may want a button that a user can click to show the record for an employee's supervisor. The following example illustrates the event procedure you would use for the button's Click event:
Private Sub cmdShowSuper_Click()
Dim frmEmployees As Form
Dim rstEmployees As DAO.Recordset
Dim varBookMark As Variant
Dim strEmployee As String
Dim strSuper As String
Set frmEmployees = Screen.ActiveForm
'Open the Recordset.
Set rstEmployees = frmEmployees.RecordsetClone
varBookMark = frmEmployees.Bookmark
strEmployee = frmEmployees!FirstName & " " & frmEmployees!LastName
rstEmployees.FindFirst "EmployeeID = " & frmEmployees!ReportsTo
If rstEmployees.NoMatch Then
MsgBox "Couldn't find " & strEmployee & "'s supervisor."
Else
frmEmployees.Bookmark = rstEmployees.Bookmark
strSuper = frmEmployees!FirstName & " " & frmEmployees!LastName
MsgBox strEmployee & "'s supervisor is " & strSuper
frmEmployees.Bookmark = varBookMark
End If
rstEmployees.Close
Set rstEmployees = Nothing
Set frmEmployees = Nothing
End Sub
If you have used another database or programming environment, you may be accustomed to referring to record numbers. For example, you may have written code that opens a text file and thereafter refers to specific records by their relative position in the file. The first record in the file would be record 1, the second would be record 2, and so on.
In Access databases, your view of records (a Recordset) is usually a subset of the records in one or more tables. Because the actual number of records in a Recordset can change at any time, especially in a multiuser environment, there's no absolute record number you can always use to refer to a particular record. The AbsolutePosition property isn't the same as a record number, because this property changes if a lower-numbered record is deleted.
Furthermore, records returned in a Recordset object appear in no particular order, unless the Recordset was created with a query that includes an ORDER BY clause, or is a table-type Recordset with an index. Record numbers are usually meaningless in a Recordset object.
Instead of record numbers, DAO provides bookmarks to uniquely identify a particular record. A given record retains its unique bookmark for the life of the Recordset.
Dynasets based on certain linked tables, such as Paradox tables that have no primary key, don't support bookmarks, nor do forward-only-type Recordset objects.
You can determine whether a given Recordset object supports bookmarks by checking the value of the Bookmarkable property, as in the following example:
If rstLinkedTable.Bookmarkable Then
MsgBox "The underlying table supports bookmarks."
Else
MsgBox "The underlying table doesn't support bookmarks."
End If
Note If you try to use bookmarks on a Recordset object that doesn't support bookmarks, a run-time error occurs.
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 an unstored parameter query that returns all records in the Orders table whose order date is between the two dates. It adds all values in the OrderID field in the query's Recordset to an array.
Function OrdersFromTo(strDateFrom As Variant, strDateTo As Variant) _
As Variant
Dim dbsNorthwind As DAO.Database
Dim rstOrders As DAO.Recordset
Dim qdfOrders As DAO.QueryDef
Dim strSQL As String
Dim intPosition As Integer
Dim aryOrderIDs() As Long
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
strSQL = "PARAMETERS [DateFrom] DateTime, [DateTo] DateTime; "
strSQL = strSQL & "SELECT * FROM Orders WHERE OrderDate BETWEEN "
strSQL = strSQL & "[DateFrom] AND [DateTo]"
'Create an unstored parameter query.
Set qdfOrders = dbsNorthwind.CreateQueryDef("", strSQL)
'Set the query parameters.
qdfOrders.Parameters("DateFrom") = strDateFrom
qdfOrders.Parameters("DateTo") = strDateTo
'Open a forward-only snapshot on the query.
Set rstOrders = qdfOrders.OpenRecordset(dbOpenSnapshot, dbForwardOnly)
'Load all the OrderIDs in the query into an array that the caller
'of the function can use.
intPosition = 1
Do While rstOrders.EOF = False
ReDim Preserve aryOrderIDs(1 To intPosition)
aryOrderIDs(intPosition) = rstOrders!OrderID
intPosition = intPosition + 1
rstOrders.MoveNext
Loop
OrdersFromTo = aryOrderIDs()
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function
See Also For more information about parameter queries, type create parameter queries in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Many of the changes you may otherwise perform in a loop can be done more efficiently with an update or delete query using SQL. The following example creates a QueryDef object to update the Employees table and then runs the query:
Dim dbsNorthwind As DAO.Database
Dim qdfChangeTitles As DAO.QueryDef
Set dbsNorthwind = CurrentDb
Set qdfChangeTitles = dbsNorthwind.CreateQueryDef("")
qdfChangeTitles.SQL = "UPDATE Employees SET Title = " & _
"'Account Executive' WHERE Title = " & _
"'Sales Representative'"
'Invoke query.
qdfChangeTitles.Execute dbFailOnError
You can replace the entire SQL string in this example with a stored parameter query, in which case the procedure would prompt the user for parameter values. The following example shows how the previous example may be rewritten as a stored parameter query:
Dim dbsNorthwind As DAO.Database
Dim qdfChangeTitles As DAO.QueryDef
Dim strSQLUpdate As String
Dim strOld As String
Dim strNew As String
Set dbsNorthwind = CurrentDb
strSQLUpdate = "PARAMETERS [Old Title] Text, [New Title] Text; " & _
"UPDATE Employees SET Title = [New Title] WHERE " & _
"Title = [Old Title]"
'Create the unstored QueryDef object.
Set qdfChangeTitles = dbsNorthwind.CreateQueryDef("", strSQLUpdate)
'Prompt for old title.
strOld = InputBox("Enter old job title:")
'Prompt for new title.
strNew = InputBox("Enter new job title:")
'Set parameters.
qdfChangeTitles.Parameters("Old Title") = strOld
qdfChangeTitles.Parameters("New Title") = strNew
'Invoke query.
qdfChangeTitles.Execute
Note A delete query is much more efficient than code that loops through a Recordset modifying or deleting records one at a time.
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 dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
rstEmployees.MoveFirst
Do Until rstEmployees.EOF
If rstEmployees!Title = "Sales Representative" Then
rstEmployees.Edit
rstEmployees!Title = "Account Executive"
rstEmployees.Update
End If
rstEmployees.MoveNext
Loop
Note If you don't use the Edit method before you try to change a value in the current record, a run-time error occurs. If you edit the current record and then move to another record or close the Recordset object without first using the Update method, your changes are lost without warning. For example, omitting the Update method from the preceding example results in no changes being made to the Employees table.
You can also terminate the Edit method and any pending transactions without saving changes by using the CancelUpdate method. While you can terminate the Edit method just by moving off the current record, this isn't practical when the current record is the first or last record in the Recordset, or is a new record. It's generally simpler to use the CancelUpdate method.
Dynaset-type Recordset objects can be based on a multiple-table query containing tables with a one-to-many relationship. For example, suppose you want to create a multiple-table query that combines fields from the Orders and Order Details tables. Generally speaking, you can't change values in the Orders table because it's on the "one" side of the relationship. Depending on your application, however, you may want to be able to make changes to the Orders table. To make it possible to freely change the values on the "one" side of a one-to-many relationship, use the dbInconsistent constant of the OpenRecordset method to create an inconsistent dynaset. For example:
Set
rstTotalSales = dbs.OpenRecordset("Sales Totals" ,,dbInconsistent)When you update an inconsistent dynaset, you can easily destroy the referential integrity of the data in the dynaset. You must take care to understand how the data is related across the one-to-many relationship and to update the values on both sides in a way that preserves data integrity.
The dbInconsistent constant is available only for dynaset-type Recordset objects. It's ignored for table, snapshot, and forward-only-type Recordset objects, but no compile or run-time error is returned if the dbInconsistent constant is used with those types of Recordset objects.
Even with an inconsistent Recordset, some fields may not be updateable. For example, you can't change the value of an AutoNumber field, and a Recordset based on certain linked tables may not be updateable.
You can delete an existing record in a table or dynaset-type Recordset object by using the Delete method. You can't delete records from a snapshot-type Recordset object. The following example deletes all the duplicate records in the Shippers table:
Sub DeleteDuplicateShippers()
Dim dbsNorthwind As DAO.Database
Dim rstShippers As DAO.Recordset
Dim strSQL As String
Dim strName As String
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Shippers ORDER BY CompanyName, ShipperID"
Set rstShippers = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)
'If no records in Shippers table, exit.
If rstShippers.EOF Then Exit Sub
strName = rstShippers![CompanyName]
rstShippers.MoveNext
Do Until rstShippers.EOF
If rstShippers![CompanyName] = strName Then
rstShippers.Delete
Else
strName = rstShippers![CompanyName]
End If
rstShippers.MoveNext
Loop
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function
When you use the Delete method, Microsoft Jet immediately deletes the current record without any warning or prompting. Deleting a record doesn't automatically cause the next record to become the current record; to move to the next record you must use the MoveNext method. However, keep in mind that after you've moved off the deleted record, you cannot move back to it.
If you try to access a record after deleting it on a table-type Recordset, you'll get error 3167, "Record is deleted." On a dynaset, you'll get error 3021, "No current record."
If you have a Recordset clone positioned at the deleted record and you try to read its value, you'll get error 3167 regardless of the type of Recordset object. Trying to use a bookmark to move to a deleted record will also result in error 3167.
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 dbsNorthwind As DAO.Database
Dim rstShippers As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstShippers = dbsNorthwind.OpenRecordset("Shippers")
rstShippers.AddNew
rstShippers!CompanyName = "Global Parcel Service"
.
. ' Set remaining fields.
.
rstShippers.Update
When you use the AddNew method, Microsoft Jet prepares a new, blank record and makes it the current record. When you use the Update method to save the new record, the record that was current before you used the AddNew method becomes the current record again.
The new record's position in the Recordset depends on whether you added the record to a dynaset- or a table-type Recordset object. If you add a record to a dynaset-type Recordset, the new record appears at the end of the Recordset, no matter how the Recordset is sorted. To force the new record to appear in its properly sorted position, you can either use the Requery method or re-create the Recordset object.
If you add a record to a table-type Recordset, the record appears positioned according to the current index, or at the end of the table if there is no current index. Because Microsoft Jet version 3.0 or later allows multiple users to create new records in a table simultaneously, your record may not appear right at the end of the Recordset as it did in previous versions of Microsoft Jet. Be sure to use the LastModified property rather than the MoveLast method to move to the record you just added.
Note If you use the AddNew method to add a new record, and then move to another record or close the Recordset object without first using the Update method, your changes are lost without warning. For example, omitting the Update method from the preceding example results in no changes being made to the Shippers table.
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.
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 Access databases differs from the behavior of ODBC data sources, such as Microsoft SQL Server™. For example, if a database is connected to a file server, and the file server stops before a transaction has had time to commit its changes, then your database could be left in an inconsistent state. If you require true transaction support with respect to durability, you should investigate the use of a client/server architecture. For more information about client/server architecture, see Chapter 16, "Multiuser Database Solutions," in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Microsoft Jet supports transactions through the DAO BeginTrans, CommitTrans, and Rollback methods of the Workspace object. The basic syntax is shown in the following table.
| Method | Operation |
| workspace.BeginTrans | Begins the transaction |
| workspace.CommitTrans | Posts the transaction, writing its updates to the permanent database objects |
| workspace.Rollback | Cancels the transaction |
The following example changes the job title of all sales representatives in the Employees table of the Northwind sample database. After the BeginTrans method starts a transaction that isolates all of the changes made to the Employees table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved with the Update method.
Sub ChangeTitle()
Dim wrkCurrent As DAO.Workspace
Dim dbsNorthwind As DAO.Database
Dim rstEmployee As DAO.Recordset
On Error GoTo ErrorHandler
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbsNorthwind = CurrentDB
Set rstEmployee = dbsNorthwind.OpenRecordset("Employees")
wrkCurrent.BeginTrans
Do Until rstEmployee.EOF
If rstEmployee!Title = "Sales Representative" Then
rstEmployee.Edit
rstEmloyee!Title = "Sales Associate"
rstEmployee.Update
End If
rstEmployee.MoveNext
Loop
If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
wrkCurrent.CommitTrans
Else
wrkCurrent.Rollback
End If
rstEmployee.Close
dbsNorthwind.Close
wrkCurrent.Close
Set rstEmployee = nothing
Set dbsNorthwind = Nothing
Set wrkCurrent = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
When you use transactions, all databases and Recordset objects in the specified Workspace object are affected—transactions are global to the workspace, not to a specific database or Recordset. If you perform operations on more than one database or within a workspace transaction, the Commit and Rollback methods affect all the objects changed within that workspace during the transaction.
Note You can also use the BeginTrans, CommitTrans, and Rollback methods with the DBEngine object. In this case, the transaction is applied to the default workspace, which is
DBEngine.Workspaces(0)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, Microsoft Access 97, and Microsoft Access 2000 as external installable ISAM databases. However, unlike other external data sources, the Jet database engine does support transactions on Microsoft Access version 2.x databases.
To determine whether or not a Database or Recordset object supports transactions, you can check the value of its Transactions property. A value of True indicates that the object does support transactions, and a value of False indicates that the object doesn't support transactions.
In previous versions of Access, it was generally recommended that you use transactions as a performance enhancement. Now all transactions for DAO add, update, and delete operations are performed internally and automatically. In most situations, this automatic support provides your application with the best possible performance. However, there may be situations where you want to fine-tune transaction behavior. You can do this by creating and modifying various settings in the Microsoft® Windows® Registry.
See Also For information about tuning Registry settings used by Microsoft Jet, see "Adjusting Windows Registry Settings to Improve Performance" in Chapter 7, "Optimizing Your Application."
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 dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strFirstName As String
Dim strLastName As String
Dim strTitle As String
Set dbsNorthwind = CurrentDb
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
rstEmployees.MoveFirst
strFirstName = rstEmployees!FirstName
strLastName = rstEmployees!LastName
strTitle = rstEmployees!Title
See Also For more information about extracting data from a record, see "Reading and Writing Data" later in this chapter.
To copy one or more entire records, you can create a two-dimensional array and copy records one at a time. You increment the first subscript for each field and the second subscript for each record.
A fast way to do this is with the GetRows method. The GetRows method returns a two-dimensional array. The first subscript identifies the field and the second identifies the row number, as follows:
varRecords(intField, intRecord)
The following example uses an SQL statement to retrieve three fields from a table called Employees into a Recordset object. It then uses the GetRows method to retrieve the first three records of the Recordset, and it stores the selected records in a two-dimensional array. It then prints each record, one field at a time, by using the two array indexes to select specific fields and records.
To clearly illustrate how the array indexes are used, the following example uses a separate statement to identify and print each field of each record. In practice, it would be more reliable to use two loops, one nested in the other, and to provide integer variables for the indexes that step through both dimensions of the array.
Sub GetRowsTest()
Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim varRecords As Variant
Dim intNumReturned As Integer
Dim intNumColumns As Integer
Dim intColumn As Integer
Dim intRow As Integer
Dim strSQL As String
On Error GoTo ErrorHandler
Set dbsNorthwind = CurrentDb
strSQL = "SELECT FirstName, LastName, Title FROM Employees"
Set rstEmployees = dbsNorthwind.OpenRecordset(SQL, dbOpenSnapshot)
varRecords = rstEmployees.GetRows(3)
intNumReturned = UBound(varRecords, 2) + 1
intNumColumns = UBound(varRecords, 1) + 1
For intRow = 0 To intNumReturned - 1
For intColumn = 0 To intNumColumns - 1
Debug.Print varRecords(intColumn, intRow)
Next intColumn
Next intRow
rstEmployees.Close
dbsNorthwind.Close
Set rstEmployees = Nothing
Set dbsNorthwind = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
You can use subsequent calls to the GetRows method if more records are available. Because the array is filled as soon as you call the GetRows method, you can see why this approach is much faster than copying one field at a time.
Notice also that you don't have to declare the Variant as an array, because this is done automatically when the GetRows method returns records. This enables you to use fixed-length array dimensions without knowing how many records or fields will be returned, instead of using variable-length dimensions that take up more memory.
If you're trying to retrieve all the rows by using multiple GetRows calls, use the EOF property to be sure that you're at the end of the Recordset. The GetRows method may return fewer rows than you request. If you request more that the remaining number of rows in a Recordset, for example, the GetRows method only returns the rows that remain. Similarly, if it can't retrieve a row in the range requested, it doesn't return that row. For example, if the fifth record cannot be retrieved in a group of ten records that you're trying to retrieve, the GetRows method returns four records and leaves the current record position on the record that caused a problem—and doesn't generate a run-time error. This situation may occur if a record in a dynaset was deleted by another user. If it returns fewer records than the number requested and you're not at the end of the file, you need to read each field in the current record to determine what error the GetRows method encountered.
Because the GetRows method always returns all the fields in the Recordset object, you may want to create a query that returns just the fields that you need. This is especially important for OLE Object and Memo fields.
See Also For more information about OLE Object and Memo fields, see "The OLE Object and Memo Data Types" later in this chapter.
The default collection of a Recordset object is its Fields collection. This collection includes a single Field object that corresponds to each field (or column) in the Recordset. Each Field object has a set of properties that uniquely identifies the field name, data type, and so on, as well as the value of the field in the current record. You use the Field objects in a Recordset object to read and set values for the fields in the current record of the Recordset object.
You manipulate a field by using a Field object and its methods and properties. For example, you can:
You can identify a Field object by its DAO Name property, which corresponds to the column name in the table from which the data in the field was retrieved. The Fields collection is the default collection of a Recordset object. Therefore, you can refer to the LastName field in the rstEmployees Recordset in any of the following ways:
rstEmployees.Fields("LastName")
rstEmployees!LastName
rstEmployees![LastName]
When using the ! operator, you must include brackets around a field name when it contains spaces. For example, the statement:
strEmployee = rstEmployees!Last Name
will not compile, but the statement:
strEmployee = rstEmployees![Last Name]
will compile with no problems.
Within the Fields collection, each Field object can also be identified by its index:
rstEmployees.Fields(0)
The index enables you to walk through the collection in a loop, replacing the index with a variable that is incremented with each pass through the loop. Objects in a collection are numbered starting with zero, so the first Field object in the Fields collection is number 0, the second is 1, and so on. The field order is determined by the underlying table. Fields are usually numbered in the order retrieved when the Recordset object is opened. One drawback to this approach is that you can't be certain which field will be referred to, because the underlying table structure may change, fields may be added or deleted, and so on.
To help you determine the order of fields in a Fields collection, the Field object supports the OrdinalPosition property, which you can use to get or set a field's position relative to other fields in the collection. You can set the OrdinalPosition property to any positive integer to change the field order when data is displayed in a form, copied to an array or a Microsoft Excel worksheet, and so on.
When writing code that refers to fields within a loop, it's more efficient to refer to Field objects rather than to refer to fields by their names. The following example shows a more efficient way of writing the ChangeTitle procedure discussed earlier in this chapter. Instead of referring to the Title field as
rstEmployees!TitleSub ChangeTitle()
Dim wrkCurrent As DAO.Workspace
Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim fldTitle As DAO.Field
On Error GoTo ErrorHandler
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbsNorthwind = CurrentDB
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
Set fldTitle = rstEmployees.Fields("Title")
wrkCurrent.BeginTrans
Do Until rstEmployees.EOF
If fldTitle = "Sales Representative" Then
rstEmployees.Edit
fldTitle = "Sales Associate"
rstEmployees.Update
End If
rstEmployees.MoveNext
Loop
If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
wrkCurrent.CommitTrans
Else
wrkCurrent.Rollback
End If
rstEmployees.Close
dbsNorthwind.Close
wrkCurrent.Close
Set fldTitle = Nothing
Set rstEmployees = Nothing
Set dbsNorthwind = Nothing
Set wrkCurrent = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
A Field object has a Type property that can be set to one of the following 12 DAO data types.
| Type property setting | Data type |
| dbBoolean | Boolean |
| dbByte | Byte |
| dbInteger | Integer |
| dbLong | Long |
| dbCurrency | Currency |
| dbSingle | Single |
| dbDouble | Double |
| dbDate | Date/Time |
| dbText | Text |
| dbLongBinary | OLE Object |
| dbMemo | Memo |
| dbGUID | GUID |
For a Field object on a Recordset, the Type property is read-only. However, you must be aware of the field type when copying data to or from a field in code or a "Type mismatch" error may occur. For example, you cannot copy Text data to an Integer field.
The Type property of a Field object on a Recordset is determined by the underlying table from which the record was retrieved. If you create the table and its fields by using DAO data-definition language (DDL) statements, you can easily determine the data type of the table's fields.
If you're accessing external data through an installable ISAM driver, the data types within external tables may be different from those defined within Microsoft Jet. The installable ISAM driver for the external data source converts external data types into their closest equivalent DAO data type.
The GUID data type is used to store a globally unique identifier, a unique string of digits that identifies OLE objects, Microsoft SQL Server remote procedure calls, and other entities that need a unique reference identification.
Note The GUID data type is also used in the Database object's ReplicaID property to identify a replica.
For Field objects declared as type Text, you must set the Size property, which indicates the length of the longest string that can be stored in the field. All other types of Field objects have their Size property set automatically.
If you need to store monetary values, use fields of type Currency. Don't use any of the number data types (such as Single) for currency values, because numbers to the right of the decimal may be rounded during calculations. The Currency data type always maintains a fixed number of digits to the right of the decimal.
In some tables, you'll want to store a series of sequential numbers to uniquely identify records. For example, you may want to start customer order records at order number 1 and begin counting upward.
Access can automatically insert unique numbers in a field, saving your application the effort of generating unique identifiers to be used within a primary key field. To take advantage of this capability, define a field with the Long data type and set the dbAutoIncrField constant in the Field object's Attributes property. Auto-incrementing fields start at 1 and increment sequentially. Fields of this type are also referred to as AutoNumber fields.
If you want to establish a primary key/foreign key relationship between two tables by using an AutoNumber field, make sure that the foreign key field is also defined as Long.
You can also set the DAO DefaultValue property of a Field object on a TableDef object to a special value called GenUniqueId( ). This causes a random number to be assigned to this field whenever a new record is added or created. The field's Type property must be Long.
Note A Field object's data type is read/write before the field is appended to a table's Fields collection, and read-only after it's appended.
OLE Object and Memo fields are collectively referred to as large value fields because they are typically much larger than fields of other data types. OLE Object fields consist of binary data up to 1.2 gigabytes in size. This type is used to store pictures, files, or other raw binary data. Memo fields are used to store lengthy text and numbers, such as comments or explanations. The size of a Memo field is limited by the maximum size of the database.
Records within a Recordset object must fit on the 2K data pages supported by the Microsoft Jet database engine. Each Field object you include in the table definition counts toward this 2K total, including OLE Object and Memo fields. However, the amount stored for OLE Object and Memo fields is only 14 bytes per non-null field, and only 1 byte for null fields. The 14 bytes is used to store a pointer to the actual data for these fields, which is located on additional 2K pages. The amount of data committed to each text field isn't set until you actually store data in the field. You can overcommit a data page by defining more text fields than there would be room for, but no more than about 2K of actual data can be stored in a record. For example, you can define fifteen 250-byte text fields in a record, but the total number of characters stored must be less than 2K.
When you query tables containing large value fields, don't include those fields in the field list unless you need them, because returning large value fields takes time. Also, be aware that you can't index large value fields.
A snapshot- or forward-only-type Recordset object opened against large value fields in an .mdb file doesn't actually contain that data. Instead, the snapshot maintains references to the data in the original tables, the same way a dynaset refers to all data.
Sometimes you'll need to read or copy data from a large value field where you don't have sufficient memory to copy the entire field in a single statement. Instead, you have to break up the data into smaller units, or chunks, that will fit available memory. The FieldSize property tells you how large the field is, measured in bytes. Then you can use the GetChunk method to copy a specific number of bytes to a buffer, and use the AppendChunk method to copy the buffer to the final location. You then continue using GetChunk and AppendChunk until the entire field is copied.
See Also For more information about data types, type data types in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
When you read or write data to a field, you're actually reading or setting the DAO Value property of a Field object. The DAO Value property is the default property of a Field object. Therefore, you can set the DAO Value property of the LastName field in the rstEmployees Recordset in any of the following ways:
rstEmployees!LastName.Value = strName
rstEmployees!LastName = strName
rstEmployees![LastName] = strName
The tables underlying a Recordset object may not permit you to modify data, even though the Recordset is of type dynaset or table, which are usually updateable. Check the Updateable property of the Recordset to determine whether its data can be changed. If the property is True, the Recordset object can be updated.
Individual fields within an updateable Recordset object may not be updateable, and trying to write to these fields generates a run-time error. To determine whether a given field is updateable, check the DataUpdatable property of the corresponding Field object in the Fields collection of the Recordset. The following example returns True if all fields in the dynaset created by strQuery are updateable and returns False otherwise.
Function RecordsetUpdatable(strSQL As String) As Boolean
Dim dbsNorthwind As DAO.Database
Dim rstDynaset As DAO.Recordset
Dim intPosition As Integer
On Error GoTo ErrorHandler
'Initialize the function's return value to True.
RecordsetUpdatable = True
Set dbsNorthwind = CurrentDb
Set rstDynaset = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)
'If the entire dynaset isn't updatable, return False.
If rstDynaset.Updatable = False Then
RecordsetUpdatable = False
Else
'If the dynaset is updatable, check if all fields in the
'dynaset are updatable. If one of the fields isn't updatable,
'return False.
For intPosition = 0 To rstDynaset.Fields.Count - 1
If rstDynaset.Fields(intPosition).DataUpdatable = False Then
RecordsetUpdatable = False
Exit For
End If
Next intPosition
End If
rstDynaset.Close
dbsNorthwind.Close
Set rstDynaset = Nothing
Set dbsNorthwind = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function
Any single field can impose a number of criteria on data in that field when records are added or updated. These criteria are defined by a handful of properties. The DAO AllowZeroLength property on a Text or Memo field indicates whether or not the field will accept a zero-length string (""). The DAO Required property indicates whether or not some value must be entered in the field, or if it instead can accept a Null value. For a Field object on a Recordset, these properties are read-only; their state is determined by the underlying table.
See Also For more information about the DAO AllowZeroLength and Required properties, type AllowZeroLength property or Required property in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Validation is the process of determining whether data entered into a field's DAO Value property is within an acceptable range. A Field object on a Recordset may have the DAO ValidationRule and ValidationText properties set. The DAO ValidationRule property is simply a criteria expression, similar to the criteria of an SQL WHERE clause, without the WHERE keyword. The DAO ValidationText property is a string that Access displays in an error message if you try to enter data in the field that's outside the limits of the DAO ValidationRule property. If you're using DAO in your code, then you can use the DAO ValidationText for a message that you want to display to the user.
Note The DAO ValidationRule and ValidationText properties also exist at the Recordset level. These are read-only properties, reflecting the table-level validation scheme established on the table from which the current record is retrieved.
A Field object on a Recordset also features the ValidateOnSet property. When the ValidateOnSet property is set to True, Access checks validation as soon as the field's DAO Value property is set. When it's set to False (the default), Access checks validation only when the completed record is updated. For example, if you're adding data to a record that contains a large Memo or OLE Object field and that has the DAO ValidationRule property set, you should determine whether the new data violates the validation rule before trying to write the data—you should write the data when the field value is set. To do so, set the ValidateOnSet property to True. If you wait to check validation until the entire record is written to disk, you may waste time trying to write an invalid record to disk.
A dynaset-type Recordset object can include records from more than one source table. Also, within a single record, fields from different tables can be joined into new records. Sometimes it's useful to know the table from which a field originated. The SourceTable property of a Field object returns the name of the table from which the field's current data was retrieved.
Within a query, a field can be renamed for display purposes. For example, in an SQL SELECT query, the AS operator in the select field list can create an alias for one of the returned fields. In a Recordset based on an SQL query, a field that has been aliased is represented by a Field object whose DAO Name property reflects the alias, not the original field name. To find out the original field name, check the Field object's SourceField property.