DOCUMENT:Q254117 11-JAN-2001 [vbwin] TITLE :HOWTO: Display Recordset 'Pages' in MSHFlexGrid Control PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:6.0 OPER/SYS: KEYWORDS:kbVBp600 kbGrpDSVBDB kbDSupport ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Professional Edition for Windows, version 6.0 - Microsoft Visual Basic Enterprise Edition for Windows, version 6.0 ------------------------------------------------------------------------------- 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. 1. Create a new Standard EXE Visual Basic project. Form1 is created by default. 2. On the Project menu, click to select References, and then add a reference to Microsoft ActiveX Data Objects 2.x Library. 3. 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. 4. Add a Vertical ScrollBar to the Form. VScroll1 will be created by default 5. 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 Additional query words: ====================================================================== Keywords : kbVBp600 kbGrpDSVBDB kbDSupport Technology : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB600Search kbVBA600 kbVB600 Version : WINDOWS:6.0 Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.