Martin Reid
mwp.reid at qub.ac.uk
Thu May 27 13:56:12 CDT 2004
What I may have is 100,000 records which have to come down from the server in one hit. All of them. What I want to do at some point is to Page them down. First 100, Then Next 100 and so on. On the Access form I want page 1 of X with navigation to any page the user chooses. Of course they will need to navigate within each batch of records they get. Its much like an ASP application but using Access. I would like to handle the paging by Stored Procedure on SQL Server rather than ADO but will give ADO a try. What I am trying to avoid is bringing all the records down the wire. The the user can change the order by of the 100000 records and we start the process all over again. So the form opens they get 100 records ordered by "A", click next page they get the Next 100 ordered by "A". They then change the order by to "B" they get the first page again ordered by "B" and so on and so on. No filtering is allowed. THEY HAVE TO HAVE ALL 100000 records available. So we would have standard navigation buttons Next Revord Previous, First Last but we would also have Page 1 of X Then a navigation bar by page size even bringing 1000 or 2000 down would be ok. Any of this make sense?? OH and it all has to be fully updatable (<: Martin ----- Original Message ----- From: "MartyConnelly" <martyconnelly at shaw.ca> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Thursday, May 27, 2004 7:45 PM Subject: Re: [AccessD] Paging Recordsets > I am just wondering where the problem lies, with Access 2000 or was it > XP , you can set the Forms recordset to an ado recordset albeit read only. > There are workarounds to handle disconnected and editable recordsets. I > must be looking at something the wrong way. > > Dim cnn As New ADODB.Connection > Dim rst As New ADODB.Recordset > > Private Sub Command0_Click() > cnn.ConnectionString = "Provider=sqloledb;" & _ > "Data Source=(local);" & _ > "Initial Catalog=pubs;" & _ > "User ID=sa;" & _ > "Password=;" > cnn.Open > rst.Source = "Select * from authors" > rst.CursorLocation = adUseClient > rst.Open , cnn > Set Me.Recordset = rst > End Sub > > > paul.hartland at fsmail.net wrote: > > >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 > >> > >> > >> > > > > > > > > -- > Marty Connelly > Victoria, B.C. > Canada > > > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >