GetRows Method

Applies To

Dynaset-Type Recordset Object, Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object.

Description

Retrieves multiple rows of a Recordset into an array.

Syntax

varArray = object.GetRows (numrows)

The GetRows Method syntax has the following parts.

Part	Description

varArray	The name of a Variant type variable to store the returned data.
object	An object expression that evaluates to an object in the Applies To list.
numrows	The number of rows to retrieve.

Remarks

Use the GetRows method to copy one or more entire records from a Recordset. GetRows returns a two-dimensional array. The first subscript identifies the field and the second identifies the row number, as follows:

avarRecords(intField, intRecord)

To get the first field value in the second row returned, use the following:

field1 = avarRecords(0,1)

To get the second field value in the first row, use the following:

field2 = avarRecords(1,0)

If more rows are requested than are available, then only the number available are returned. Use Ubound to determine how many rows were actually fetched, as the array is sized to fit how many rows were returned. For example, if you returned the results into a variant called varA, you could determine how many rows were actually returned by using:

numReturned = Ubound(varA,2) + 1

The "+ 1" is used because the first data returned is in the 0 element of the array. The number of rows that can be fetched is constrained by available memory and should be chosen to suit your application — don't expect to use GetRows to bring your whole table into an array if it is a large table.

Because all fields of the recordset are returned in the array, including long memo and binary fields, you might want to use a query that restricts the columns returned.

After a call to GetRows, the current record is positioned at the next unread row. That is, GetRows has an equivalent positioning effect to Move numrows.

If you are trying to fetch all the rows using multiple GetRows calls, use the EOF property to be sure that you're at the end of the Recordset. GetRows returns less than the number requested either at the end of the Recordset, or if it cannot fetch a row in the range requested. For example, if a fifth record cannot be retrieved in a group of ten records that you're trying to fetch, GetRows returns four records and leaves currency on the record that caused a problem. It will not generate a run-time error. This situation might occur if a record in a dynaset has been deleted by another user.

See Also

FillCache Method, Move Method, Value Property.

Example

The following example uses an SQL statement to retrieve three fields from an Employees table into a Recordset object. It then uses the GetRows method to retrieve the first three records of the Recordset, and stores the selected records in a two-dimensional array. Each record is then printed, one field at a time, using the two array indexes to select specific fields and records.

Note

To clearly illustrate how the array indexes are used, the example uses a separate statement to identify and print each field of each record. In practice, it would be more reliable to use two loops, one nested in the other, and provide integer variables for the indexes, to step through both dimensions of the array.

Sub GetRows_Test()

    Dim dbsCurrent As Database, rstEmployees As Recordset
    Dim varRecords As Variant
 
    Set dbsCurrent = CurrentDB()
    Set rstEmployees = dbsCurrent.OpenRecordset("SELECT FirstName, " & _
        "LastName, Title FROM Employees", dbOpenSnapshot)

    varRecords = rstEmployees.GetRows(3)

    Debug.Print "First Name", "Last Name", "Title"
    Debug.Print varRecords(0, 0),
    Debug.Print varRecords(1, 0),
    Debug.Print varRecords(2, 0)

    Debug.Print varRecords(0, 1),
    Debug.Print varRecords(1, 1),
    Debug.Print varRecords(2, 1)
    Debug.Print varRecords(0, 2),
    Debug.Print varRecords(1, 2),
    Debug.Print varRecords(2, 2)

End Sub

Example (Microsoft Access)

The following example uses the GetRows method to return a two-dimensional array containing the first ten 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 Database variable pointing 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)
    ' Return first ten rows into array.
    varRecords = rst.GetRows(10)
    ' 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
End Sub

Example (Microsoft Excel)

This example copies records from a selection of the Customer recordset in the NWINDEX.MDB database to Sheet1.

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset
Dim data As Variant
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("SELECT CUSTMR_ID, CONTACT FROM Customer;")
data = rs.GetRows(6)
Sheets("Sheet1").Activate
For r = 1 to UBound(data, 2) + 1

    For c = 1 to 2
        Cells(r, c).Value = data(c - 1, r - 1)
    Next
Next
rs.Close
db.Close