GetRows Method

Applies To Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only–Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Retrieves multiple rows from a Recordset object.

Syntax

Set varArray = recordset.GetRows(numrows)

The GetRows method syntax has the following parts.

Part	Description

varArray	A Variant that stores the returned data.
recordset	An object variable that represents a Recordset object.
numrows	A Variant that is equal to the number of rows to retrieve.

Remarks Use the GetRows method to copy records from a Recordset. GetRows returns a two-dimensional array. The first subscript identifies the field and the second identifies the row number. For example, intField represents the field, and intRecord identifies the row number:

avarRecords(intField, intRecord)

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

field1 = avarRecords(0,1)

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

field2 = avarRecords(1,0)

The avarRecords variable automatically becomes a two-dimensional array when GetRows returns data.

If you request more rows than are available, then GetRows returns only the number of available rows. You can use the Visual Basic for Applications UBound function to determine how many rows GetRows actually retrieved, because the array is sized to fit the number of returned rows. For example, if you returned the results into a Variant called varA, you could use the following code to determine how many rows were actually returned:

numReturned = UBound(varA,2) + 1

You need to use "+ 1" because the first row returned is in the 0 element of the array. The number of rows that you can retrieve is constrained by the amount of available memory. You shouldn't use GetRows to retrieve an entire table into an array if it is large.

Because GetRows returns all fields of the Recordset into the array, including Memo and Long Binary fields, you might want to use a query that restricts the fields returned.

After you call GetRows, the current record is positioned at the next unread row. That is, GetRows has the same effect on the current record as Move numrows.

If you are trying to retrieve all the rows by 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 if it's at the end of the Recordset, or if it can't retrieve a row in the range requested. For example, if you're trying to retrieve 10 records, but you can't retrieve the fifth record, GetRows returns four records and makes the fifth record the current record. This will not generate a run-time error. This might occur if another user deletes a record in a dynaset-type Recordset. See the example for a demonstration of how to handle this.

See Also FillCache method, Move method, Value property.

Example

This example uses the GetRows method to retrieve a specified number of rows from a Recordset and to fill an array with the resulting data. The GetRows method will return fewer than the desired number of rows in two cases: either if EOF has been reached, or if GetRows tried to retrieve a record that was deleted by another user. The function returns False only if the second case occurs. The GetRowsOK function is required for this procedure to run.

Sub GetRowsX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strMessage As String
    Dim intRows As Integer
    Dim avarRecords As Variant
    Dim intRecord As Integer

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = dbsNorthwind.OpenRecordset( _
        "SELECT FirstName, LastName, Title " & _
        "FROM Employees ORDER BY LastName", dbOpenSnapshot)

    With rstEmployees
        Do While True
            ' Get user input for number of rows.
            strMessage = "Enter number of rows to retrieve."
            intRows = Val(InputBox(strMessage))
            If intRows <= 0 Then Exit Do

            ' If GetRowsOK is successful, print the results,
            ' noting if the end of the file was reached.
            If GetRowsOK(rstEmployees, intRows, _
                    avarRecords) Then
                If intRows > UBound(avarRecords, 2) + 1 Then
                    Debug.Print "(Not enough records in " & _
                        "Recordset to retrieve " & intRows & _
                        " rows.)"
                End If
                Debug.Print UBound(avarRecords, 2) + 1 & _
                    " records found."

                ' Print the retrieved data.
                For intRecord = 0 To UBound(avarRecords, 2)
                    Debug.Print "    " & _
                        avarRecords(0, intRecord) & " " & _
                        avarRecords(1, intRecord) & ", " & _
                        avarRecords(2, intRecord)
                Next intRecord
            Else
                ' Assuming the GetRows error was due to data
                ' changes by another user, use Requery to
                ' refresh the Recordset and start over.
                If .Restartable Then
                    If MsgBox("GetRows failed--retry?", _
                            vbYesNo) = vbYes Then
                        .Requery
                    Else
                        Debug.Print "GetRows failed!"
                        Exit Do
                    End If
                Else
                    Debug.Print "GetRows failed! " & _
                        "Recordset not Restartable!"
                    Exit Do
                End If
            End If

            ' Because using GetRows leaves the current record
            ' pointer at the last record accessed, move the
            ' pointer back to the beginning of the Recordset
            ' before looping back for another search.
            .MoveFirst
        Loop
    End With

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Function GetRowsOK(rstTemp As Recordset, _
    intNumber As Integer, avarData As Variant) As Boolean

    ' Store results of GetRows method in array.
    avarData = rstTemp.GetRows(intNumber)
    ' Return False only if fewer than the desired number of
    ' rows were returned, but not because the end of the
    ' Recordset was reached.
    If intNumber > UBound(avarData, 2) + 1 And _
            Not rstTemp.EOF Then
        GetRowsOK = False
    Else
        GetRowsOK = True
    End If

End Function

Example (Microsoft Access)

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

Example (Microsoft Excel)

This example copies selected records from 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