VBA: Save data from SQL queery in an array.

The following code is connecting to SQL and running a SQL queery. I works just fine. How do I store the data generated in the SQL queery to an Array in VBA?

Sub test()
Dim DSN As String
DSN = "Provider= ... ;Data Source= ... ;Initial Catalog= ... ;Integrated Security= ...;"
' I cannot share the name of databases and so on.
Dim Query1 As String
Dim Res1 As New ADODB.Recordset
  Query1 = "select 1,2"
  Set Conn = New ADODB.Connection
  Conn.Open (DSN)
  
  Set Res1 = Conn.Execute(Query1)
 
  Sheet1.Range("A1").CopyFromRecordset Res1

  Res1.Close
  Conn.Close
End Sub

The code above is working perfectly and the values from SQL queery is being printed in A1 and B1. Now suppose I don't want to print it in my sheets but I want to store it in an VBA array. What do I do?

Hi

Please use code tags when posting code.

You can use GetRows thus

Dim theArray
theArray = Res1.GetRows

Note that the array is transposed from the way CopyFromRecordset produces it.

Originally Posted by xlnitwit

Hi

Please use code tags when posting code.

You can use GetRows thus

Dim theArray
theArray = Res1.GetRows

Note that the array is transposed from the way CopyFromRecordset produces it.

It acutally doesn't solve my problem. I don't think TheArray is saved as an array. Later I try to refer to TheArray(2) but it wont be recognized

Then I add the following

dim newArray(1 to 2) as Integers
newArray = TheArray

Then there is a compile error: Can't assign to array. TheArray is clearly not am array.

Originally Posted by kbdb

Then I add the following

dim newArray(1 to 2) as Integers
newArray = TheArray

Then there is a compile error: Can't assign to array. TheArray is clearly not am array.

It is an array, you just apparently aren't familiar with VBA arrays. You cannot assign one array to another unless the receiving array is dynamic and of matching type.

To extract a value from a 2 dimensional array, you must, as I mentioned, provide an index for both dimensions- e.g.

msgbox theArray(1, 1)

It will be a 2 dimensional array (If you look in the locals window I'm pretty sure you'll see an array) so you need to supply both dimensions when extracting data from it.

Originally Posted by xlnitwit

It will be a 2 dimensional array (If you look in the locals window I'm pretty sure you'll see an array) so you need to supply both dimensions when extracting data from it.

How can extract data then? Suppose I want to create a new variable wich is the mean. How do I calculate that by reffering to data from TheArray?

How would you do it with a one-dimensional array?

Do you know how to use the Locals window in the VB editor? It will tell you the type, size and shape of the array.

Yes, and what size is it?

If it's (0 to 1, 0 to 0) then you can't use (2, 2) as the indices. You only have:
(0, 0)
(1, 0)
available.