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?

Barnet

A recordset is an array, and much more versatile than a VBA array (i.e., reference by column name and not just column index). Why not just use the recordset directly? I have been programming in VBA/DAO for well more than a decade and have never once used GetRows. What makes you think you need it? – Blithe

Specifically to minimize the time the connection to other ADODB objects is open. – Barnet

What about an ADO disconnected recordset, then? – Blithe

@Blithe - if you program in VBA/DAO, then .GetRows is easier to write data to another app like Excel as one block without having to populate via a loop. Example: ArrayA = Rst.GetRows(Rst.RecordCount) – Orgiastic

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

Ripple

DAO does not require a .MoveLast unless you want an accurate recordcount, which you hardly ever actually need (you only need to know if the recordset returned records and the recordcount is always 1 or more if the DAO recordset returned records). I see no reason to use ADO, which is DEAD, DEAD, DEAD. DAO is part of a live database engine that is under constant development, and seems to me to be the future for working with Jet/ACE data. – Blithe

I see now from looking testing GetRows that if you pass no parameter to it, it retrieves only one row. You could choose an arbitrarily large number and skip the .MoveLast and avoid the performance hit. Or you could get an accurate recordcount by checking the table's .RecordCount property, though that doesn't work on linked tables, so you'd have to use the back end directly (not all that difficult to code and surely much more efficient than a MoveLast on a large recordset), but it won't work on anything other than a single-table recordset. – Blithe

Still using ADO in 2020. very much not dead. Very much faster than any other method of accessing SQL Server. – Bindweed

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.

Albric

Answer 3

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

Sienkiewicz

Answer 4

In Access you can do an indexed seek. That is deftly the fastest method and even faster than searching in arrays.

    Set rs = CreateObject("ADODB.Recordset")
    rs.CursorLocation = adUseServer
    rs.Open "MyData", CurrentProject.Connection, , , adCmdTableDirect
    rs.Index = "fieldX"

    rs.Seek fieldXvalue

If you just loop through the entire table than an array really is the fastest. One exception: In theory, if you have a numeric primary key, you can set the index to its exact same position in the array, so no rs.find or rs.seek at all, you can just access it like array(index) and that ist REALLY fast. I didn't benchmark against indexed seek, but it may be faster.

Stationary

Answer 5

Here's my solution:

Dim rs As New ADODB.Recordset
Dim ConnectionString as String
Dim arr as variant

ConnectionString = "DRIVER=SQL Server;SERVER=serverName\instance;Trusted_Connection=Yes"
CommandText = "SELECT * FROM table"

rs.Open CommandText, ConnectionString 
arr = rs.GetRows
rs.Close

Tirade