SQL Results to Array: GetRows
Post date: Oct 22, 2013 9:30:57 PM
Here is a quick way to get database results into an array.
In Using ADO with ListObject's QueryTable we viewed a way to get database results into Excel's cells. But sometimes we want the data in an array for processing only within VBA code. Fortunately, this is simple with GetRows. In the coding example, vAry receives all Employee records from Norhwind's sample database.
Coding Example:
Dim cn As Object 'Connection
Dim rs As Object 'Recordset
Dim vAry As Variant 'Variant Array
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Northwind 2003.accdb;"
rs.Open "Select * From [Employees 2003]", cn
vAry = rs.getRows
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
References
GetRows http://msdn.microsoft.com/en-us/library/windows/desktop/ms677500(v=vs.85).aspx
Northwind MS Access database: https://www.dropbox.com/s/1y8zah663lyezzq/Northwind%202003.accdb?dl=1