SQL Query Destination as an array varable

[casey.chambliss]

I know how to get the results for a SQL query into a sheet like this:

ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), Sql:=sqlstring).Refresh

I need my code for the SQL query to put the results into an array
varable (or something like that) that I can refrence.

My goal in all of this is to run a second query and loop through each
instance of the 1st query without having to put the 1st query results
on a sheet and then iterating through it.

The best way to describe what I'm doing is I want to 1st query the list
of sales people in a table and then run a 2nd query that will give me
the sales data for each of the sales people and put that info into the
workbook.

If I can get this to work, we can add sales people to the database and
I won't have to revist my code.

If my approach is wrong please help me.

 

[AA2e72E]

I need my code for the SQL query to put the results into an array varable
(or something like that) that I can refrence.

Try:

Set RS = CreateObject("ADODB.Recordset")
RS.Open "Your SQL", "Your Connection String"
YourVar= RS.GetRows
Set RS = Nothing

At this point, YourVar is an array (2-dimensional).

 

[casey.chambliss]

Here is my macro:

Sub SQL2ARRAY()
connstring = "ODBC;DSN=DBSRV;UID=;PWD=;Database=BPC_Reports"
sqlstring = "SELECT * FROM BPC_Reports.dbfficers"
Set RS = CreateObject("ADODB.Recordset")
RS.Open "sqlstring", "connstring"
YourVar = RS.GetRows
Set RS = Nothing
MsgBox YourVar
End Sub

But I'm getting this error:

Run-time error '-2147467259 (80004005)':
Automation error
Unspecified error

I'm completely new to SQL queries and using them in excel macros...

What am I missing?