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

Don't really do much Excel programming, but doesn't Range("A1") refer to a single cell? Should it be something like Range("A1:E15")? — Sparky
– Sparky, Commented Nov 10, 2011 at 21:27

user194076 · Accepted Answer · 2011-11-10 23:16:03Z

1

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.

answered Nov 10, 2011 at 23:16

user194076

9,05524 gold badges103 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AFJ Over a year ago

I looked online and found some examples that define db as as: Dim db as Database My problem is that this datatype is not available to me for some reason, anybody know why this may be?

user194076 Over a year ago

I'm not a VBA expert, but seems like you do not have appropriate reference. What's the exact error?

AFJ Over a year ago

Error: User Defined type not defined. I looked at my available reference and I have the following selected: Excel, Forms, and Office Object Library. The available ones are: OLE Automation, Solver, VBA Project, Chart 14 Type Library, Microsoft Graph, Powerpoint and Word Object Library, and Visual Basic for Applications Extensibility

user194076 Over a year ago

Take a look at this: dbforums.com/microsoft-access/… and this: bytes.com/topic/access/answers/… maybe soemthing will solve your problem

Collectives™ on Stack Overflow

Excel VBA - How to Populate Array from SQL Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related