Martin Reid
mwp.reid at qub.ac.uk
Fri May 28 10:35:48 CDT 2004
Havnt got to that part yet (<: Martin ----- Original Message ----- From: "Charlotte Foust" <cfoust at infostatsystems.com> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Friday, May 28, 2004 4:25 PM Subject: RE: [AccessD] Paging Recordsets > It it has to be updatable, you are now officially in the "unbound" camp. > Unfortunately, Access doesn't provide for an unbound continuous form. > How were you planning on presenting these "pages"? > > Charlotte Foust > > -----Original Message----- > From: Martin Reid [mailto:mwp.reid at qub.ac.uk] > Sent: Thursday, May 27, 2004 10:56 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Paging Recordsets > > > 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 > > > > -- > _______________________________________________ > 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 >