CodeItBetter Programming Another VB Programming Blog

'Database - How to Use ADO to page through records 10 at a time
Option Explicit
 
Private m_CombinedNames As String
 
' Display the first 10 records.
Private Sub cmdList_Click()
    ' Reset m_CombinedNames
    ' to select the first record.
    m_CombinedNames = ","
 
    ' Get the next 10 records.
    cmdNext.Enabled = True
    cmdNext_Click
End Sub
 
' Display the next 10 records.
Private Sub cmdNext_Click()
    Dim db_file As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim txt As String
    Dim i As Integer
 
    ' Get the database name.
    db_file = App.Path
    If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
    db_file = db_file & "People.mdb"
 
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" & _
        "Persist Security Info=False"
    conn.Open
 
    ' Get the next 10 records.
    Set rs = conn.Execute("SELECT TOP 10 * FROM Employees " & "WHERE LastName + ',' + _
        FirstName > " & "'" & m_CombinedNames & "' ORDER BY LastName, " & "FirstName")
 
    ' Display the records.
    Do Until rs.EOF
        i = i + 1
        txt = txt & vbCrLf & Format$(rs!EmployeeId, "@@@") & " " & _
            Format$(rs!LastName, "!@@@@@@@@@@@@@") & Format$(rs!FirstName, "!@@@@@@@@@@@@@")
        m_CombinedNames = rs!LastName & "," & rs!FirstName
        rs.MoveNext
    Loop
 
    ' See if we ran out of records.
    If i < 10 Then
        txt = txt & vbCrLf & "<END>"
        cmdNext.Enabled = False
    End If
 
    ' Display the data.
    If Len(txt) > 0 Then txt = Mid$(txt, 3)
    txtEmployees.Text = txt
End Sub