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 since APIs do not accept recordsets as parameters, this is the only way you can do it. Normally, you would define the array, and then loop through the rows, appending data to the array as you went.

Dim varMyArray() As Variant Dim varField As Variant

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

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


Do While Not rst.EOF

For Each varField In rst.Fields varMyArray(rst.AbsolutePosition, _

varField.OrdinalPosition) = varField Next varField rst.MoveNext


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("Table1", dbOpenSnapshot) varMyArray = rst.GetRows(12 0)

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 that exist, Access only returns the number of rows that were actually present in the recordset.

Be a little judicious when using this technique, because Access will return 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.

Was this article helpful?

0 0

Post a comment