How to store query data into dynamic array?

I have the code which can successfully pull data from database, but my question is related to something else.

I do NOT want to copy data to sheet Test, for two reasons:
1) The return query data may have many records (exceeding maximum excel 2013 row limitation 1,048,576), which will cause the code bug
2) I will need further process the data, it is very time consuming to loop through excel data.

So I am thinking about sorting data in memory, processing data in memory should be much faster than processing data in spreadsheet. I will copy final data (after being processed) into spreadsheet. That being said, I do NOT want this line of code (ThisWorkbook.Sheets("Test").Cells(7, 5).CopyFromRecordset CVRs) at this point, I want to sort the data into an dynamic array, since I don't know how many records from CVRs.

Maybe I need a line to code to count the number of returned records? If the number of records is k, then

Dim DynArrayField1 (0 To k-1) As Long
Dim DynArrayField2 (0 To k-1) As Long
Dim DynArrayField3 (0 To k-1) As Long
//Assume there are 3 fields in returned query records

How to count the number of record? How to store the records into the arrays?


Thanks.

Dim CVRs As ADODB.Recordset
Dim CVQuery As String
Dim CVField As Field



.....................



CVQuery = "Select Field1, Field2, Field3 From Tablename Where ....."
            
                        
CVRs.Open CVQuery, Cn
           
ThisWorkbook.Sheets("Test").Cells(7, 5).CopyFromRecordset CVRs

Originally Posted by CK76

Recordset is an array, and more versatile than VBA array. For an example, you can reference by column index or name.

If you want to minimize duration of connection being open. Then you could...

Dim ar
ar = CVRs.GetRows

Oh, thanks for the information. For GetRows , I assume ar is multiple dimensional array. Since I need further process the data, how can I refer to specific fields (Field1, Field2, Field3) within the array?

Let us say, I want to break it down to 3 small arrays for each field: ar1, ar2, ar3 . That being said ar(0) = ar1(0)+ ar2(0) + ar3(0); ar(1) = ar1(1)+ ar2(1) + ar3(1); .....

How to print array into sheet? For example, how to copy/paste ar to sheets("Test").Cells(1,1)?

Thanks.

Originally Posted by CK76

Sample below, for filling array with single column data (using Field Index#, you could use it's name in place).
Do note that .RecordCount will return different result depending on cursor type of Recordset object.
I typically use... "adOpenStatic" This is specified in Recordset.Open method.

Dim ar
With CVRs
    If .RecordCount > 1 Then
        ReDim ar(0 To .RecordCount - 1, 1)
        i = 0
        Do Until .EOF
            ar(i, 0) = .Fields(0)
            .MoveNext
            i = i + 1
        Loop
    End If
End With
Range("A1").Resize(UBound(ar), 1) = ar

Thank you. I will try the code later.