GetRows Method Example (MDB)

The following example uses the GetRows method to return a two-dimensional array containing all rows of data in a Recordset object:

Sub RowsArray()
    Dim dbs As Database, rst As Recordset, strSQL As String
    Dim varRecords As Variant, intI As Integer, intJ As Integer
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Build SQL statement that returns specified fields.
    strSQL = "SELECT FirstName, LastName, HireDate " _
        & "FROM Employees"
    ' Open dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset(strSQL)
    ' Move to end of recordset.
    rst.MoveLast
    ' Return to first record.
    rst.MoveFirst
    ' Return all rows into array.
    varRecords = rst.GetRows(rst.RecordCount)
    ' Find upper bound of second dimension.
    For intI = 0 To UBound(varRecords, 2)
        Debug.Print
        ' Find upper bound of first dimension.
        For intJ = 0 To UBound(varRecords, 1)
            ' Print data from each row in array.
            Debug.Print varRecords(intJ, intI)
        Next intJ
    Next intI
    rst.Close
    Set dbs = Nothing
End Sub