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 archive 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 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.

Answer 2

I agree it does look like it might be a reference problem.

If you are going to be sticking with access/jet then you might want to consider using DAO as all things being equal it will be faster then ADO. Here is a quick example

Public Sub Foo()
Dim aFoo As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("tblFoo")

With rst
    .MoveLast
    .MoveFirst
    aFoo = .GetRows(.RecordCount)
End With

rst.Close
db.Close

End Sub

Answer 3

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