Using Arrays with Recordsets

Sometimes you may choose to populate an array with data from a recordset. Perhaps you're intending to pass the array to a Windows API, and because APIs do not accept recordsets as parameters, this is the only way you can do it. Typically, you would define the array and then loop through the rows, appending data to the array as you went, as the following code illustrates:

Dim varMyArray() As Variant
Dim varField As Variant

Set rst = dbs.OpenRecordset("Table1", dbOpenSnapshot)

rst.MoveLast
ReDim varMyArray(rst.RecordCount, rst.Fields.Count)
rst.MoveFirst

Do While Not rst.EOF
    For Each varField In rst.Fields
	varMyArray(rst.AbsolutePosition, varField.OrdinalPosition) = varField
    Next varField
    rst.MoveNext
Loop

But DAO provides a nifty little method to do all this for you -GetRows. GetRows returns a two-dimensional array containing all the column data for the specified number of rows, with the first element specifying the row and the second specifying the column.

Dim varMyArray As Variant
Set rst = dbs.OpenRecordset("SELECT Field1, Field2 FROM Table1", dbOpenSnapshot)
varMyArray = rst.GetRows(120)

You don't have to define the array's rows; in fact, you don't even have to declare it as an array; just define it as a variant. Access takes care of the rest.

After you call GetRows, the recordset's cursor position is set to the next unread row. You can specify the number of rows to return, but if you specify more rows than exist, Access returns only the number of rows actually present in the recordset.

Be a little judicious when using this technique, because Access returns all the recordset columns, regardless of their data type. You could end up with Memo and OLE (object linking and embedding) data in your array. It is wiser to filter the recordset, so you only have the data you actually need.