[AccessD] Paging Recordsets

Martin Reid mwp.reid at qub.ac.uk
Wed May 26 13:31:09 CDT 2004


I know. I want to to the same thing with data from SQL Server in Access.

Martin


----- Original Message ----- 
From: "MartyConnelly" <martyconnelly at shaw.ca>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, May 26, 2004 6:40 PM
Subject: Re: [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
>
>
>
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>




More information about the AccessD mailing list