| 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
rsExample.MoveNext
lngHowManyRecs = lngHowManyRecs + 1
Loop
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.
|
