[AccessD] Paging Recordsets

paul.hartland at fsmail.net paul.hartland at fsmail.net
Thu May 27 04:31:25 CDT 2004


Martin,
Stumped on this one the only way I can think of doing something similar is create a temporary table, write the result set into the temporary table and have an additional counter field 1 to ??? then with an access query have something like 
SELECT * FROM tblTemp WHERE tblTempCounterField>=1 And tblTempCounterField<=100

then change the query to 
SELECT * FROM tblTemp WHERE tblTempCounterField>=101 And tblTempCounterField<=200

etc etc as the user presses the next or previous page button.
Paul Hartland
PS if you come across a better solution I would like to know how to do it for future reference.





Message date : May 26 2004, 07:33 PM
>From : "Martin Reid" 
To : "Access Developers discussion and problem solving" 
Copy to : 
Subject : Re: [AccessD] Paging Recordsets
I know. I want to to the same thing with data from SQL Server in Access.

Martin


----- Original Message ----- 
From: "MartyConnelly" 
To: "Access Developers discussion and problem solving"

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
>

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm


More information about the AccessD mailing list