Copying Entire Records to an Array

To copy one or more entire records, you can create a two-dimensional array and copy records one at a time. You increment the first subscript for each field and the second subscript for each record.

A fast way to do this is with the GetRows method. The GetRows method returns a two-dimensional array. The first subscript (an index into the array) identifies the field, and the second identifies the record number, as follows:

varRecords(intField, intRecord)

The following example uses an SQL statement to retrieve three fields from a table called Employees and place them in a Recordset object. It then uses the GetRows method to retrieve the first three records of the Recordset object, and stores the selected records in a two-dimensional array. Each record is then printed, 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 this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database
Dim rst As Recordset
Dim varRecords As Variant
Dim intNumReturned As Integer, intNumFields As Integer
Dim intField As Integer, intRecord As Integer
Dim strSQL As String

Set dbs = OpenDatabase(strDbPath)
strSQL = "SELECT FirstName, " & _
	"LastName, Title FROM Employees"
' Open snapshot-type recordset.
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

' Return array using GetRows.
varRecords = rst.GetRows(3)
' Determine upper bound of second dimension.
intNumReturned = UBound(varRecords, 2)
' Determine upper bound of first dimension.
intNumFields = UBound(varRecords, 1)

' Print each value in array.
For intRecord = 0 To intNumReturned
	For intField = 0 To intNumFields
		Debug.Print varRecords(intField, intRecord)
	Next intField
	Debug.Print
Next intRecord

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, this approach is much faster than using assignment statements to copy one field at a time.

Note also that you don’t have to dimension the Variant variable 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 records by using multiple calls to the GetRows method, use the EOF property to be sure that you’re at the end of the Recordset object. The GetRows method returns fewer than the number requested if it’s at the end of the Recordset object or if it cannot retrieve a record in the range requested. 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 does not generate a run-time error. This situation may occur if a record in a dynaset was deleted by another user. If fewer records were returned than the number requested and you’re not at the end-of-file position, you will 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 you need. This is especially important for Memo and Long Binary (OLE Object) fields.

Microsoft Excel Users You can import the contents of a Recordset object into a range on a Microsoft Excel worksheet with the CopyFromRecordset method. This method begins to copy at the current record of the result set; when the transfer is completed, the Recordset object pointer is positioned just past the last record, or at the end-of-file position. For more information, see the Microsoft Excel Visual Basic Help file.