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

• 1. GetRows http://msdn.microsoft.com/en-us/library/windows/desktop/ms677500(v=vs.85).aspx

  2. Northwind MS Access database: https://www.dropbox.com/s/1y8zah663lyezzq/Northwind%202003.accdb?dl=1