MartyConnelly
martyconnelly at shaw.ca
Fri May 28 11:47:37 CDT 2004
Why not consider the use of a VB DataGrid Control on the form Maybe a third party one. Martin Reid wrote: >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 >> >> >> > > > -- Marty Connelly Victoria, B.C. Canada