Excel VBA - How to Populate Array from SQL Query

Question

I've been trying to write a piece of code that would allow me to query an SQL DB and use the returned values to populate a combobox.

The query runs fine when I ask it to return the values to the worksheet, however I don't want them there, I simply want to store them in an array to be used by the combobox.

Here's what I have so far...

Sub testQuery()
Dim varConn As String
Dim SQL As String
Dim test As String


Range("A1").CurrentRegion.ClearContents

varConn = "ODBC; DSN=Traceability DB;UID=XXX;PWD=XXX"

SQL = "Select Distinct ""Date"" from testtable"

With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"), SQL:=SQL)
    .Refresh
End With

UserForm1.Show

End Sub

I'm not sure how to replace Range("A1") to an array.

Note: this is being used on the latest version of Excel for the MAC.

Thanks for the help

Solution

Save it in record set. Something like this:

Set rs = db.OpenRecordset("Select Distinct ""Date"" from testtable")

To access records in record set use GetRows Something like this:

data = rs.GetRows(j)

and then loop through data.