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

Please Login or Register  to view this content.

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


Thanks.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Thank you. I will try the code later.