How to: Extract Data from a Record in a DAO Recordset

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

Copying a Single Field

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

  Dim dbsNorthwind As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim strFirstName As String
Dim strLastName As String
Dim strTitle As String
Set dbsNorthwind = CurrentDb
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")
rstEmployees.MoveFirst
strFirstName = rstEmployees!FirstName
strLastName = rstEmployees!LastName
strTitle = rstEmployees!Title

Copying Entire Records to an Array

To copy one or more 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 to use the GetRows method, which 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 do not 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 are trying to retrieve all the rows by using multiple GetRows calls, use the EOF property to be sure that you are at the end of the Recordset. The GetRows method may return fewer rows than you request. If you request more than the remaining number of rows in a Recordset, for example, the GetRows method returns only the rows that remain. Similarly, if it cannot retrieve a row in the range requested, it does not return that row. For example, if the fifth record cannot be retrieved in a group of 10 records that you are trying to retrieve, the GetRows method returns four records and leaves the current record position on the record that caused a problem, and does not generate a run-time error. This situation may occur if a record in a dynaset was deleted by another user. If it returns fewer records than the number requested and you are 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.