About   -   Contact   -   Purchase   -   Search   -   What's New

ADO Page Properties
Displaying large amounts of data, typically thousands of records, can present a number of obstacles. Firstly, navigation can be problematic in that the scrollbar becomes hyper-sensitive, jumping hundreds of records at the slightest touch. Secondly, overheads arise as a result of retrieving and populating forms with vast amounts of data.

The simple solution is to offer a filter on the data such as some chronological selection or an alphabetical filter such as surname grouping. However, such filtering opportunities may not exist, in which case, the only option is to display the data as a fixed number of records at a time, in a similar manner to which web searches return their results. To aid this approach, ADO offers three properties, PageSize, AbsolutePage and PageCount designed to control and manage the retrieval of large recordsets. The PageSize property set the number of records returned at a time and AbsolutePage determines which 'page' is returned, where the page is the group of records of size set by PageSize. Then the number of pages is simply calculated by ADO and accessed through the PageCount property. 

Eg., if a recordset contains 500 records, and the PageSize is set to 50, the AbsolutePage can take the values 1 to 10, and the PageCount is 10. The example below, assumes a large recordset, rsExample, containing 1000's of records.

  Private Function DisplayPage(lngMaxRecs as Long, _
    lngPage as Long, rsExample as ADODB.Recordset)

      Dim intRow as Integer
      Dim lngHowManyRecs as Long
      Dim lngMaxPages as Long

      rsExample.PageSize = lngMaxRecs
      rsExample.AbsolutePage = lngPage

      lngHowManyRecs = 0
      lngMaxPages = rsExample.PageCount

      lblPage = "Page " & lngPage & " of " & lngMaxPages

      Do Until rsExample.EOF Or lngHowManyRecs >= lngMaxRecs

          ' intRow included to determine the row number
          ' for use in DataGrids
          intRow = rsExample.AbsolutePosition - _
            (rsExample.PageSize * (rsExample.AbsolutePage - 1))

          'populate form with data here

          lngHowManyRecs = lngHowManyRecs + 1


  End Function
In the example function here, the page index and page size are passed as parameters to the function, controlling which set of data is displayed. This type of function could be called from a navigation control such as a series of cmdbuttons representing movefirst, moveprevious, movenext and movelast actions. The PageSize property, lngMaxRecs, could be set in a menu option or combobox etc. and the AbsolutePage property, lngPage would be set to 1, decremented, incremented or set to PageCount, respectively with respect to the 4 navigation controls above.

This method represents far greater efficiency over returning the entire recordset or holding a global recordset open and progressed throughout it. By contrast, the method above allows the recordset to be closed and the object killed after the function is executed. One point to note, controls must be in place to ensure a valid AbsolutePage parameter is passed within  the range of 1 to PageCount, ideally in the calling code where the recordset is created.


Top of Page

Legal Notice

Ken Howe 2011