[AccessD] Paging Recordsets

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






More information about the AccessD mailing list