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 >