How To Display Recordset 'Pages' in an MSFlexGrid Control Without Loading Entire Recordset into the Grid (254117)

SUMMARY

Generally it is not a good idea to populate a Grid control with more than a 1000 rows, but there might be times when it is necessary to populate a Grid with many times this number. When you do this, however, your application takes a large performance hit at the beginning, sometimes taking several minutes after the recordset is loaded to actually populate the grid.

This article demonstrates one way to display thousands of records without having to actually load them all at the same time. The is done by "paging" through the recordset and only displaying the number of records that corresponds to the number of rows visible in the grid. This particular sample uses the MSFlexGrid control and is intended to show how to handle a read-only grid. With additional code, you can update the underlying recordset but that is beyond the scope of this particular article.

MORE INFORMATION

The following steps accomplish the task of populating the MSFlexgrid without loading the entire recordset into the grid.

Create a new Standard EXE Visual Basic project. Form1 is created by default.
On the Project menu, click to select References, and then add a reference to Microsoft ActiveX Data Objects 2.x Library.
On the Toolbar, right-click and select Components. Select the Microsoft FlexGrid control from the list of available controls. Add an MSFlexgrid Component to Form1. MSFlexGrid1 is created by default.
Add a Vertical ScrollBar to the Form. VScroll1 will be created by default

Paste the following code into the forms General Declarations section:

Dim Rs As New ADODB.Recordset
Dim RecPages As Long
Dim PageSize As Long
Dim CurrentStart As Long
Dim JustSet As Boolean

Private Sub Form_Load()
Dim Cn As New ADODB.Connection
Set Cn = New ADODB.Connection
Set Rs = New ADODB.Recordset
Cn.CursorLocation = adUseClient

'Jet Connection And Recordset
'Cn.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Northwind.mdb"
'Rs.Open "Select * from Customers", Cn, adOpenStatic, adLockOptimistic

'SQL Connection and Recordset
Cn.Open "PROVIDER=SQLOLEDB;server=YourSQLServer;uid=sa;pwd=;database=pubs;"
Rs.Open "select * from [authors]", Cn, adOpenStatic, adLockOptimistic

CurrentStart = 0

'Set the Scroll bar to be in front of the grid
VScroll1.ZOrder 0
End Sub

Sub FillGrid(StartRec As Long, NumRecs As Long)
MSFlexGrid1.Clear
If StartRec <= 1 Then StartRec = 1
Dim MyString As String
Dim X As Long
MSFlexGrid1.Visible = False
MSFlexGrid1.Clear
MSFlexGrid1.Rows = 1
MSFlexGrid1.Cols = Rs.Fields.Count + 1

MSFlexGrid1.AddItem ""
For X = StartRec To StartRec + NumRecs
    If X < Rs.RecordCount - 1 Then
        Rs.AbsolutePosition = X
        MyString = "" & Chr(9)
        
        For J = 0 To Rs.Fields.Count - 1
            If IsNull(Rs(J)) Then
                A$ = " "
            Else
                A$ = Rs(J)
            End If
            MyString = MyString & A$ & Chr(9)
        Next J
        MSFlexGrid1.AddItem MyString
    End If
Next X
MSFlexGrid1.AddItem ""
MSFlexGrid1.Visible = True
JustSet = True
MSFlexGrid1.TopRow = 2

End Sub

Private Sub Form_Resize()
JustSet = True
MSFlexGrid1.Top = 30
MSFlexGrid1.Left = 50
MSFlexGrid1.Visible = True
MSFlexGrid1.Height = Me.Height - 500
MSFlexGrid1.Width = Me.Width - 200
MSFlexGrid1.Clear
MSFlexGrid1.Cols = 20
MSFlexGrid1.Rows = 100
MSFlexGrid1.TopRow = 1
X = 1
Do Until X = 100
    If MSFlexGrid1.RowIsVisible(X) = False Then
        PageSize = X - 1
        Exit Do
    End If
    X = X + 1
Loop

MSFlexGrid1.Rows = 1
MSFlexGrid1.Cols = 1
RecPages = (Rs.RecordCount / PageSize) - 1
VScroll1.Max = RecPages
VScroll1.Min = 0

FillGrid CurrentStart, PageSize
VScroll1.Top = MSFlexGrid1.Top + 30
VScroll1.Left = (MSFlexGrid1.Width - VScroll1.Width) + 15
MSFlexGrid1.LeftCol = 1
If MSFlexGrid1.ColWidth(Rs.Fields.Count) + MSFlexGrid1.ColPos(Rs.Fields.Count) > MSFlexGrid1.Width Then
TempVAr = MSFlexGrid1.RowHeight(1)
Else
TempVAr = 0
End If

VScroll1.Height = (((PageSize + 1) * MSFlexGrid1.RowHeight(1)) + ((MSFlexGrid1.Height - 60) - (PageSize + 1) _
* MSFlexGrid1.RowHeight(1))) - TempVAr

End Sub

Private Sub MSFlexGrid1_Scroll()

If JustSet = True Then JustSet = False: Exit Sub

If MSFlexGrid1.Row >= MSFlexGrid1.Rows - 3 Then
    CurrentStart = CurrentStart + 1
    If CurrentStart > Rs.RecordCount - 1 Then CurrentStart = Rs.RecordCount - 1
    FillGrid CurrentStart, PageSize
    MSFlexGrid1.SetFocus
    MSFlexGrid1.Row = MSFlexGrid1.Rows - 3
    If (VScroll1.Value + 1) * PageSize < CurrentStart Then VScroll1.Value = VScroll1.Value + 1
    Exit Sub
End If

If MSFlexGrid1.Row <= 2 Then
    CurrentStart = CurrentStart - 1
    If CurrentStart < 1 Then CurrentStart = 1
    FillGrid CurrentStart, PageSize
    MSFlexGrid1.SetFocus
    MSFlexGrid1.Row = 3
    MSFlexGrid1.Row = 2
    If VScroll1.Value - 1 > 0 Then VScroll1.Value = VScroll1.Value - 1
    Exit Sub
End If
    
    Call FillGrid(CurrentStart, PageSize)
    MSFlexGrid1.SetFocus
    
    
End Sub


Private Sub VScroll1_Change()
If VScroll1.Value * PageSize >= CurrentStart Then
CurrentStart = CurrentStart + (PageSize)
Else
CurrentStart = CurrentStart - (PageSize)
End If
If CurrentStart < 1 Then CurrentStart = 1
FillGrid CurrentStart, PageSize
End Sub