How to populate an array with recordset data

Question

I am attempting to move data from a recordset directly into an array. I know this is possible, but specifically I want to do this in VBA as this is being done in MS Access 2003.

Typically I would do something like the following to achieve this:

    Dim vaData As Variant 
    Dim rst As ADODB.Recordset

    ' Pull data into recordset code here...

    ' Populate the array with the whole recordset.
    vaData = rst.GetRows 

What differences exist between VB and VBA which makes this type of operation not work?

What about performance concerns? Is this an "expensive" operations?

Answer 1

The following code works for me:

Dim rst           As ADODB.Recordset
Dim vDat          As Variant

Set rst = CurrentProject.Connection.Execute("select * from tblTemp4")
vDat = rst.GetRows

Do a debug-compile, as mentioned this could be ref issue. As noted, some perfer DAO, but keep in mind DAO requires you to do a movelast. ADO does not. These days, ADO or DAO really comes down to your preferance, and performance is rarely an issue. ADO tends to be a bit cleaner of a object model, but whatever your familer with is likey the best choice in most cases

Answer 2

The usual reason that your sample would not work is that the proper library for ADO has not been referenced (Tools->References, Microsoft ActiveX Data Objects x.x Library), otherwise, it should be fine.