AbsolutePage & PageSize  to Fragment Data Streams

One of the challenges in Web Development is getting a database recordset with a high volume of records and not overwhelm the user with inforamation. Streaming all the records back to the client browser uses up alot of bandwidth and user time.

A better way is to break the recordset up into pages and stream only a single page back to the client, allowing for a next and previous capability. Using the ADO PageSize and AbsolutePage Methods the recordset now can be liken to a series of virtual pages containing PageSize number of records, with the AbsolutePage as an index into the recordset.

After setting the AbsolutePage a counter is required while moving through the recordset to ensure the number of returning records does not exceed the PageSize

 
Option Explicit
Dim db As Connection
Dim lCurrentPage As Long

Private Sub cmdNext_Click()
    lCurrentPage = lCurrentPage + 1
    Call LoadListBox(lCurrentPage)
End Sub

Private Sub cmdPrevious_Click()
    If lCurrentPage > 1 Then
        lCurrentPage = lCurrentPage - 1
        Call LoadListBox(lCurrentPage)
    End If
End Sub

Private Sub Form_Load()
    
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=d:\api\absolutepage\test.mdb;"

    lCurrentPage = 1
    Call LoadListBox(lCurrentPage)

End Sub
Private Sub LoadListBox(lPage As Long)
    Dim adoPrimaryRS As ADODB.Recordset
    Dim lPageCount As Long
    Dim nPageSize As Integer
    Dim lCount As Long

    nPageSize = 7
    Set adoPrimaryRS = New Recordset
    adoPrimaryRS.Open "select * from numbers", db, adOpenStatic, adLockOptimistic

    adoPrimaryRS.PageSize = nPageSize
    lPageCount = adoPrimaryRS.PageCount
    If lCurrentPage > lPageCount Then
        lCurrentPage = lPageCount
    End If
    
    txtPage.Text = lPage
    
    adoPrimaryRS.AbsolutePage = lCurrentPage
    
    With lbxRecords
        .Clear
        lCount = 0
        Do While Not adoPrimaryRS.EOF
            .AddItem adoPrimaryRS("aNumber")
            lCount = lCount + 1
            If lCount = nPageSize Then
                Exit Do
            End If
            adoPrimaryRS.MoveNext
        Loop
        
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not db Is Nothing Then
        db.Close
    End If
    Set db = Nothing
End Sub