Microsoft KB Archive/254117
Article ID: 254117
Article Last Modified on 7/1/2004
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
This article was previously published under Q254117
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.
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
Keywords: kbhowto KB254117