Send results of SQL query to an array

I have successfully connected to a database and extracted the data I want to a worksheet using the 'Activesheet.QueryTables.Add' approach.

However, I don't want the data on a worksheet at all. Ideally I want the data to be put into an array which I can then extract data from to enter on a user form or in a comment box.

I can do this by downloading the data to a worksheet and then creating an array from that data but this seems long-winded. Does anybody know of an easier way to get my query results into an array?

Use ADO (ActiveX Data Objects) and SQL to return the results of the query to a recordset.
Then loop through the records in the recordset doing whatever you need to do. An array isn't required for this.

You didn't say what database, so here's an ADO sample to get data from a workbook into an array (requires a reference to the Microsoft ActiveX Data Objects 2.n library):

Please Login or Register  to view this content.

Thanks, I seem to be on the right track now but I have run into difficulties with the provider and connection string.

The database is an IBM iSeries AS/400 server. I have a driver for this configured in Data Sources(ODBC) area of the Windows control panel. The driver is called 'iSeries Access ODBC Driver' and the data source name is As400a. When I use the 'QueryTables.Add' method I set the connection property to "ODBC;DSN=As400a;" and it works fine, however, I have no idea what provider or connection string to use with ADO as everything I have tried has failed.

Try this site for all things related to connections strings (link is to the page for AS400)

I've tested the following code with a User DSN called AS400a connected to a MS Access database containing a 'Computers' table.

Just change the SQL string to query the table(s) in your database as required.

Please Login or Register  to view this content.

That's worked perfectly! And the website link was very useful too! Thank you guys.