GetRows Method 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