MartyConnelly
martyconnelly at shaw.ca
Fri May 28 01:04:15 CDT 2004
What about putting a VB datagrid control on the form and dumping the recordset data into that.? Martin Reid wrote: >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 >> >> >> > > > -- Marty Connelly Victoria, B.C. Canada