[AccessD] Paging Recordsets

Paging  is quite commonly done on ASP websites

You just set or use  three properties of the ADO recordset
' Create recordset and set the page size (no of records per page)
Set objPagingRS = New ADODB.Recordset
objPagingRS.PageSize = iPageSize
' Get the count of the pages using the given page size
iPageCount = objPagingRS.PageCount
' Move to the selected page number
    objPagingRS.AbsolutePage = iPageCurrent

Here is some quickly  cobbled sample code stolen from http://www.asp101.com

Search on "paging" as there several methods available including placing 
in an array
There are probably articles floating around as to best method for 
performance


Sub AdoPaging()
'stolen from 
http://www.asp101.com/samples/viewasp.asp?file=db%5Fpaging%2Easp
' BEGIN USER CONSTANTS
Dim CONN_STRING As String
Dim CONN_USER As String
Dim CONN_PASS As String

CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Program Files\Microsoft 
Office\Office\Samples\Northwind.mdb;" & _
           "User Id=admin;" & "Password="

' Our SQL code - overriding values we just set
' Comment out to use Access
'CONN_STRING = "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
'    & "Initial Catalog=samples;Connect Timeout=15;" _
'    & "Network Library=dbmssocn;"
'CONN_USER = "samples"
'CONN_PASS = "password"
' END USER CONSTANTS

' Declare our vars
Dim iPageSize As Long      'How big our pages are
Dim iPageCount  As Long    'The number of pages we get back
Dim iPageCurrent  As Long  'The page we want to show
Dim strOrderBy  As String    'A fake parameter used to illustrate 
passing them
Dim strSQL  As String        'SQL command to execute
Dim iRecordsShown  As Long    'Loop controller for displaying just 
iPageSize records
Dim I  As Long                'Standard looping var
Dim objPagingConn As ADODB.Connection
Dim objPagingRS As ADODB.Recordset

' Get parameters
' set number of records per page

   iPageSize = 10 ' You could easily allow users to change this

' Set  page to show or default to 1

    iPageCurrent = 2


' If you're doing this script with a search or something
' you'll need to pass the sql from page to page.  I'm just
' paging through the entire table so I just hard coded it.
' What you show is irrelevant to the point of the sample.
'strSQL = "SELECT * FROM sample ORDER BY id;"

strOrderBy = "'Customer ID'"
'strOrderBy = "last_name,first_name,sales"

' Build our SQL String using the parameters we just got.
strSQL = "SELECT * FROM customers ORDER BY " & strOrderBy & ";"


Debug.Print strSQL

' Now we finally get to the DB work...
' Create and open our connection
Set objPagingConn = New ADODB.Connection
'objPagingConn.Open CONN_STRING, CONN_USER, CONN_PASS
objPagingConn.Open CONN_STRING
' Create recordset and set the page size
Set objPagingRS = New ADODB.Recordset
objPagingRS.PageSize = iPageSize

' You can change other settings as with any RS
'objPagingRS.CursorLocation = adUseClient
objPagingRS.CacheSize = iPageSize

' Open RS
objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly, 
adCmdText

' Get the count of the pages using the given page size
iPageCount = objPagingRS.PageCount

' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1

' Check page count to prevent bombing when zero results are returned!
If iPageCount = 0 Then
    Debug.Print "No records found!"
Else
    ' Move to the selected page
    objPagingRS.AbsolutePage = iPageCurrent

    ' Start output with a page x of n line
   
    ' Show field names in the top row
    Dim strtitles As String
    strtitles = ""
    For I = 0 To objPagingRS.Fields.Count - 1
        strtitles = strtitles & objPagingRS.Fields(I).Name & " "
    Next 'I
      Debug.Print strtitles & vbCrLf

    ' Loop through our records and ouput 1 row per record
    iRecordsShown = 0
    Dim strFields As String
    strFields = ""
    Do While iRecordsShown < iPageSize And Not objPagingRS.EOF
   
        For I = 0 To objPagingRS.Fields.Count - 1
   
            strFields = strFields & objPagingRS.Fields(I) & ","

        Next 'I
        Debug.Print strFields & vbCrLf
        'clear strfields shown
        strFields = ""

        ' Increment the number of records we've shown
        iRecordsShown = iRecordsShown + 1
        ' Can't forget to move to the next record!
        objPagingRS.MoveNext
    Loop

    ' All done - close table

End If

' Close DB objects and free variables
objPagingRS.Close
Set objPagingRS = Nothing
objPagingConn.Close
Set objPagingConn = Nothing

End Sub


Martin Reid wrote:

>Anyone every done any work on paging large recordsets in Access. I want to
>look at the possibility of breaking up massive recordsets returned from SQL
>Server in page sets of say 100 records. Rather than pull them all at the
>start I want the user to be able to select Page 1, Page etc etc then move
>freely between pages.
>
>Only 100 records (or less) would be taken of the server at one time.
>
>I cannot use filters at the client end. All records MUST be available?
>
>MArtin
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada