Database - How do I get just a range of records, as you do in search results?

[si_the_geek]

When showing lots of data, many programs and web sites (including this one) show just a range of results, with links to Next/Previous/etc pages. This is known as "paging" the data.

Doing this makes navigating the results easier, and also means that the program/site runs faster, as less data is worked with at any one time. But how do you do it in ADO?


Well it is actually quite simple - you just use a few more properties than usual.

Before opening the recordset, you set PageSize (the number of records per "page") and CacheSize (how many records to store in memory at one time). For the best speed, CacheSize should be the same value as PageSize.

Next you open the recordset as normal, and then simply set AbsolutePage to be the page to load (the first page is 1). You can read PageCount to find out how many pages of data there are in the recordset (ie: the maximum value you can use for AbsolutePage).


To show data you would normally loop until you reach EOF, but in this case you do not want to do that, as you only want to load the right number records for a page - but you must still check for EOF, as there may not be enough records on the last page for it to be full!

Here is an example of how you might do all of this:

'set up the recordset, and load the data:
Dim rs as ADODB.Recordset
  Set rs = New ADODB.Recordset
  rs.PageSize = 10
  rs.CacheSize = 10
  rs.Open "SELECT field1 FROM table1", cn, adOpenStatic, adLockReadOnly, adCmdText
 
'use the data
Dim intRecord As Integer
  If rs.EOF Then
    Msgbox "No data found!"
  Else
      'for this example, go to the second page of data
    rs.AbsolutePage = 2  
      'show the data for this page..
    For intRecord = 1 To rs.PageSize
        '..do what we want with the data in this record
      MsgBox rs.Fields("field1").value
 
        '..move to the next record in this page
      rs.MoveNext
        '..if we have run out of records (which we may on the last page) exit the loop
      If rs.EOF Then Exit For
 
    Next intRecord
  End If
 
'tidy up
  rs.Close
  Set rs = Nothing

..and in the attachment below is an example of how you can extend this to allow the user to move from one page to another.