20,000 records plus in MSFlexgrid?

[not_seen]

Is this possible?

I have about 20K records and am trying to display them in a grid. However, when I try to test it I get an out of memory error (in development environment).

Any way around this?

Should I use a different grid?

It takes about 20 seconds per 500 records. Any way to speed this up?

[Iouri]

You can put as many records to msfg as your memory allows. But why in the right mind you have to put 20000 records? Do you expect that somebody will scrool throug it?
Here the way around it. You can load one page at a time and on scroll event load a new page.

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 example 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.

'add references ADO2.x
'Components
'VScroll1
'MSFlexGrid1


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:\TestDB\TestDB.mdb"
Rs.Open "Select * from PRQ", 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)
Dim J As Integer
Dim A$

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()
Dim X As Integer
Dim TempVar As Integer

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




Iouri Boutchkine
iouri@hotsheet.com