VBFlexGrid Control (Replacement of the MSFlexGrid control)

[Nouyana]

You have the ShowInfoTips property set to True ?

I've tried both options (True and False) with no result.

[Krool]

I've tried both options (True and False) with no result.

IVBFlexDataSource2 can be implemented in addition to a IVBFlexDataSource implementation which is set to .FlexDataSource.

So is your IVBFlexDataSource2 impl "together" with IVBFlexDataSource?

[Nouyana]

So is your IVBFlexDataSource2 impl "together" with IVBFlexDataSource?

Yes. You can see all of my testing code above (here and here) or with the project file in the "FlexDataSource.zip" attachment. The "CreatingDatabase.zip" attachment contains the code for creating the database file I use.

FlexDataSource.zip

CreatingDatabase.zip

[Nouyana]

How to use it? I've been added this routines to my cFlexDataSource class:

Implements IVBFlexDataSource2


Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
   IVBFlexDataSource2_GetFlags = FlexDataSourceToolTipText
End Function

Private Function IVBFlexDataSource2_GetToolTipText(ByVal Field As Long, _
                                                   ByVal Record As Long) As String   
   Debug.Print "IVBFlexDataSource2_GetToolTipText"
End Function

Private Sub IVBFlexDataSource2_SetToolTipText(ByVal Field As Long, _
                                              ByVal Record As Long, _
                                              ByVal NewValue As String)
   Debug.Print "IVBFlexDataSource2_SetToolTipText"
End Sub

But nothing was printed in my Immediate window.

I don't have this problem when I'm working with a small number of records. This code works fine:

Private Const MAX_ROWS = 65000

Implements IVBFlexDataSource
Implements IVBFlexDataSource2

Public RS As DAO.Recordset

'****************************************************************************
'                          Implements IVBFlexDataSource
'----------------------------------------------------------------------------
Private Function IVBFlexDataSource_GetFieldCount() As Long
  IVBFlexDataSource_GetFieldCount = RS.Fields.Count
End Function
Private Function IVBFlexDataSource_GetFieldName(ByVal Field As Long) As String
  IVBFlexDataSource_GetFieldName = RS(Field).Name
End Function
Private Function IVBFlexDataSource_GetRecordCount() As Long
  IVBFlexDataSource_GetRecordCount = MAX_ROWS
End Function
Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
                                           ByVal Record As Long) As String
   Static PrevRecord As Long
   If Record < MAX_ROWS Then
      With RS
         If (Record = PrevRecord + 1&) Then
            .MoveNext
         ElseIf (Record = PrevRecord - 1&) Then
            .MovePrevious
         Else
            .MoveFirst
            .Move Record
         End If
         IVBFlexDataSource_GetData = .Fields(Field)
      End With
   Else
      Exit Function
   End If
   PrevRecord = Record
End Function
Private Sub IVBFlexDataSource_SetData(ByVal Field As Long, _
                                      ByVal Record As Long, _
                                      ByVal NewData As String)
   Static PrevRecord As Long
   If Record < MAX_ROWS Then
      With RS
         If (Record = PrevRecord + 1&) Then
            .MoveNext
         ElseIf (Record = PrevRecord - 1&) Then
            .MovePrevious
         Else
            .MoveFirst
            .Move Record
         End If
         .Edit
         .Fields(Field) = NewData
         .Update
      End With
   End If
   PrevRecord = Record
End Sub

'****************************************************************************
'                          Implements IVBFlexDataSource2
'----------------------------------------------------------------------------
Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
   IVBFlexDataSource2_GetFlags = FlexDataSourceToolTipText
End Function

Private Function IVBFlexDataSource2_GetToolTipText(ByVal Field As Long, _
                                                   ByVal Record As Long) As String
   Debug.Print "IVBFlexDataSource2_GetToolTipText" 'Works fine!
End Function

Private Sub IVBFlexDataSource2_SetToolTipText(ByVal Field As Long, _
                                              ByVal Record As Long, _
                                              ByVal NewValue As String)
   Debug.Print "IVBFlexDataSource2_SetToolTipText"
End Sub

Private Function IVBFlexDataSource2_GetChecked(ByVal Field As Long, _
                                               ByVal Record As Long) As Integer
   '
End Function

Private Sub IVBFlexDataSource2_SetChecked(ByVal Field As Long, _
                                          ByVal Record As Long, _
                                          ByVal NewValue As Integer)
   '
End Sub

[Nouyana]

If I load more than 1,5 million records using FlexDataSource, the grid starts to slow down (read only) and it is almost impossible to work with it. If I work using DataSource, I have no such problems. The problem is appear only after pressing Ctrl+Down keys, that is, after last records are reached.

Probably the cause of this problem is the same as with the ToolTips.

Finally, the question is: Should I limit the number of records by code, or "virtual mode" can do all the job for me?

[Nouyana]

Hi, Krool! Can you add the ItemData functionality to the DropDown ComboBoxes? Just an array of long integers. Something like that:

With VBFlexGrid1
   .ColComboMode(COL_COMBODROPDOWN) = FlexComboModeDropDown
   .ColComboItems(COL_COMBODROPDOWN) = "Arnold|Bob|Charlie"
   .ColComboItemsData(COL_COMBODROPDOWN) = "1|2|3"
End With

[Nouyana]

It makes no sense to display a msgbox if you want active user resizing. It makes sense if you want to cancel the user resizing. In this case ensure to make a Cancel = True before the MsgBox.

The MsgBox can be used in a error handler code in some cases. And if Enabled = False, then VBFlexGrid will hang.

Can you automatically set Cancel to True if a modal window is invoked from the BeforeUserResize event?

[Calcu]

@Nouyana, i can see your are actively testing this, do you have the same problem like i have?

I found (post 846) that if you use 2 grids in a same form in a MDI environment, you can hang IDE easily.

Send a test project on post 855.

Do you have the same problem under XP ?

[Nouyana]

@Nouyana, i can see your are actively testing this, do you have the same problem like i have?

No, I use the OCX version.

I know, but when I need to update it, it's better using it embedded in my soft.

You shoul use this compilation utility. It allows you to work in IDE using OCX, and then compile the EXE-file using source Krool's files. But first you have to solve a few puzzles that MountainMan left in his program for some reasons

[Calcu]

Thanks, i will take a look at it.

Meanwhile, i'm trying to use the code used on the uctabmdi control, that has this code:

'Stop all subclassing
Private Sub Subclass_StopAll()
  Dim i As Long
  
  i = UBound(sc_aSubData())                                                             'Get the upper bound of the subclass data array
  Do While i >= 0                                                                       'Iterate through each element
    With sc_aSubData(i)
      If .hWnd <> 0 Then                                                                'If not previously Subclass_Stop'd
        Call Subclass_Stop(.hWnd)                                                       'Subclass_Stop
      End If
    End With
    
    i = i - 1                                                                           'Next element
  Loop
End Sub

Just Trying to figure where to get the subclass data array :-)

[Nouyana]

deleted

[Krool]

Yes. You can see all of my testing code above (here and here) or with the project file in the "FlexDataSource.zip" attachment. The "CreatingDatabase.zip" attachment contains the code for creating the database file I use.

FlexDataSource.zip

CreatingDatabase.zip

I tried your demo. For me IVBFlexDataSource2_GetToolTipText fires always. Also at the end of the table.

Regarding the slowleness. If I comment out the code on IVBFlexDataSource_GetData in your cFlexDataSource then the grid is fast, always. So it must be something with the .record moving of the DAO db.

[Schmidt]

Regarding the slowleness.
If I comment out the code on IVBFlexDataSource_GetData in your cFlexDataSource then the grid is fast...

Aside from potential slowdowns in DAO-Rs-navigation/caching strategies -
there's one "other problem" with such "huge-sized" DataSources.

When I run similar Code with huge-sized SQLite-Rs (2.5Mio Records x 4 Columns = 10Mio Cells) - then I have:
- a mem-consumption of the SQLite-Rs of about 100MByte (about 10Byte per Rs-Cell on average)
- after the binding went through, the grid causes an additional mem-consumption of about 1GByte (~100 byte per cell)

So, there's probably a 2D-Array-Redim (for Cell-individual Formatting or something) in the Grid-Ctl -
which might be an indirect cause for "slowdowns everywhere" due to straining VBs mem-allocator (CoTaskMemAlloc) -
regarding "additional allocations, which happen after the Binding of such a huge DataSource took place".

Easiest solution perhaps is, to introduce a "Cell-based OwnerDrawing" instead of Flex-internal drawing,
to avoid these Grid-internal allocations for the formatting-infos.

We use this Cell-OwnerDrawing-mode with the vsFlexGrid, when we bind via vsFlexDataSource to "large log-files" for example.

Olaf

[Nouyana]

Can I somehow set the CellPicture to a PNG image?

[Nouyana]

I tried your demo. For me IVBFlexDataSource2_GetToolTipText fires always. Also at the end of the table.

I've just tested it under different OS'es. It seems, that this issue is only under WinXP.

[Nouyana]

Regarding the slowleness. If I comment out the code on IVBFlexDataSource_GetData in your cFlexDataSource then the grid is fast, always. So it must be something with the .record moving of the DAO db.

It seems so. Can you share some example of good code for IVBFlexDataSource_GetData sub for huge recordsets? Any of ADO or DAO.

[Nouyana]

Easiest solution perhaps is, to introduce a "Cell-based OwnerDrawing" instead of Flex-internal drawing,
to avoid these Grid-internal allocations for the formatting-infos.

The right solution is to limit the number of entries to some reasonable value (~100 000) using IVBFlexDataSource_GetRecordCount and IVBFlexDataSource_GetData. But I do not know how to do it correctly.

I'm just testing the capabilities of the Grid as well as its virtual mode.

[Nouyana]

This one works fast:

Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
                                           ByVal Record As Long) As String
   Static PrevRecord As Long
   Dim delta&, i     As Long
   
   delta = Abs(Record - PrevRecord)
   With RS
      If (Record > PrevRecord) Then
         For i = 1 To delta
            .MoveNext
         Next
      ElseIf (Record < PrevRecord) Then
         For i = 1 To delta
            .MovePrevious
         Next
      End If
      IVBFlexDataSource_GetData = .Fields(Field)
   End With
   PrevRecord = Record
End Function

EDITED: The PrevRecord variable should not be a Static. It should be a module-level variable because of using in other subs the same way.

[Nouyana]

Krool,

how to limit the number of records in IVBFlexDataSource_GetRecordCount and IVBFlexDataSource_GetData subs correctly? What if there are several hundred million records on the server in one table? We should load them in parts.

[Schmidt]

What if there are several hundred million records on the server in one table? We should load them in parts.

You misunderstand the approach somehow, because it's not Krools responsibility to "automatically limit the record-amount" -
the Grid just reacts accordingly, to what amount you told him you have, in the Interface-Callback.

If you want "paged mode", then it's up to you, to limit the amount of Records
(which you select from a given Table) - via proper SQL-instructions:
- either via Where-Clause (Select <FldList> From Table Where ID > LastPagedID)
- or better, in conjunction with a Top-Clause (Select Top 10000 <FldList> From Table Where...)

Olaf

[Nouyana]

You misunderstand the approach somehow, because it's not Krools responsibility to "automatically limit the record-amount"

I didn't say the "automatically" word. I just asked him to help.

If you want "paged mode", then it's up to you, to limit the amount of Records
(which you select from a given Table) - via proper SQL-instructions:
- either via Where-Clause (Select <FldList> From Table Where ID > LastPagedID)
- or better, in conjunction with a Top-Clause (Select Top 10000 <FldList> From Table Where...)

Yes, something like this. The problem here will arise at the time of moving through the grig, deleting or editing records. Do you have any working example?

The only possibility I see here in order not to make mistakes is to get the value of the Primary Key field from the VBFlexGrid. I don't really like to use this way. It would be better to somehow synchronize rows in Recordset and rows in FlexGrid.

Perhaps the main difficulty is that the grid should be loaded dynamically, that is not at the moment when you reached the last record of the current "page".

[Schmidt]

The problem here will arise at the time of moving through the grig, deleting or editing records.

Each time you bind a Recordset (via a *generic* behaving binding-class) -
the whole thing should "just work" (on that concrete Recordset, when the Binding-Class was implemented properly).

Since that is the case (with any given, potentially "paged" Rs),
you will just have to ensure (with your own paging-related code on the outside of the Grid),
that "the next paged Recordset" you retrieve from the DB, is "properly bound again" (via the Binding-Class).

Special care needs to be taken, in case of Delete- or Add-Operations
(where you need to "re-initiate the Binding", to reflect the now reduced or increased RecordCount properly in the Grid) -
note, that the technique shown in the SQLite-Binding assumes, that we have bound "a disconnected Rs".

Do you have any working example?

I have not used DAO for the last 20 years - and don't have any working "binding-examples".
(I primarily use SQLite, for which an example exists).

Olaf

[Nouyana]

I primarily use SQLite, for which an example exists.

I mean "paged" example

[Schmidt]

I mean "paged" example

Based on SQLite-Rs?

As said - this would come out as a "quite trivial extension" on top of the "non-paged SQLite-Rs-Demo".

Because only a few "page-navigation-buttons" (or a Pages DropDown-List) would have to be added,
which in their Click-Events ensure: "a new selected, record-limited Rs via SQL", which in turn will be bound again -
and then will behave "in the same way as the non-paged Demo is showing already"
(for the given Rs, currently in the binding-context).

Olaf

[Nouyana]

As said - this would come out as a "quite trivial extension" on top of the "non-paged SQLite-Rs-Demo".

So, just do it! Let us see your "trivial" approach.

[Schmidt]

So, just do it! Let us see your "trivial" approach.

I'd rather "you show me" (preferrably in a new thread of yours, initiated in the main-forum),
what you made out of my hints, regarding the:
"extension-code needed, to convert an existing non-paged SQLite-Rs-Demo, into a paged one".
(starting with your initial attempt, in a Zip).

Olaf

[Nouyana]

I'd rather "you show me" (preferrably in a new thread of yours, initiated in the main-forum),
what you made out of my hints, regarding the:
"extension-code needed, to convert an existing non-paged SQLite-Rs-Demo, into a paged one".
(starting with your initial attempt, in a Zip).

Olaf

Ok. In a couple of days.

[Krool]

Aside from potential slowdowns in DAO-Rs-navigation/caching strategies -
there's one "other problem" with such "huge-sized" DataSources.

When I run similar Code with huge-sized SQLite-Rs (2.5Mio Records x 4 Columns = 10Mio Cells) - then I have:
- a mem-consumption of the SQLite-Rs of about 100MByte (about 10Byte per Rs-Cell on average)
- after the binding went through, the grid causes an additional mem-consumption of about 1GByte (~100 byte per cell)

So, there's probably a 2D-Array-Redim (for Cell-individual Formatting or something) in the Grid-Ctl -
which might be an indirect cause for "slowdowns everywhere" due to straining VBs mem-allocator (CoTaskMemAlloc) -
regarding "additional allocations, which happen after the Binding of such a huge DataSource took place".

Easiest solution perhaps is, to introduce a "Cell-based OwnerDrawing" instead of Flex-internal drawing,
to avoid these Grid-internal allocations for the formatting-infos.

We use this Cell-OwnerDrawing-mode with the vsFlexGrid, when we bind via vsFlexDataSource to "large log-files" for example.

Olaf

Yes, a row with 4 columns consumes per cell 64 bytes and 20 bytes for a SafeArray on that row.
So, 2,500,000 rows * ((4 * 64) + 20) = 690,000,000 bytes (~658 MB)

I will soon optimize the TCELL structure to consume only 52 bytes. This safes in this example 100 MB.

However, the only solution would be to "omit custom cell formatting". Which can be done relatively easy in the VBFlexGrid. (not redim TCOLS structure)

Question is just in what form. Should a FlexDataSource "automatically" "omit custom cell formatting" ? Because why ownerdraw all when we get at least text and can draw based on columns / row formatting ?

[Nouyana]

Question is just in what form. Should a FlexDataSource "automatically" "omit custom cell formatting" ? Because why ownerdraw all when we get at least text and can draw based on columns / row formatting ?

Can we combine both: the automatic and the manual ways? The default would be "omit, until using".

[Arnoutdv]

Yes, a row with 4 columns consumes per cell 64 bytes and 20 bytes for a SafeArray on that row.
So, 2,500,000 rows * ((4 * 64) + 20) = 690,000,000 bytes (~658 MB)

When I once was trying to enhance to vsFlexGrid to hold even more variables per cell I didn't use a matrix for the whole grid, but a linked list holding only the elements which actually used at least one of the extended properties.
Using a dictionary with [row & "*" & col] as the key to access the index of the linked list.

[SearchingDataOnly]

Yes, a row with 4 columns consumes per cell 64 bytes and 20 bytes for a SafeArray on that row.
So, 2,500,000 rows * ((4 * 64) + 20) = 690,000,000 bytes (~658 MB)

I will soon optimize the TCELL structure to consume only 52 bytes. This safes in this example 100 MB.

However, the only solution would be to "omit custom cell formatting". Which can be done relatively easy in the VBFlexGrid. (not redim TCOLS structure)

Question is just in what form. Should a FlexDataSource "automatically" "omit custom cell formatting" ? Because why ownerdraw all when we get at least text and can draw based on columns / row formatting ?

I tested VBFlexGrid in depth a few years ago and found that one of the big differences between it and Fapoint-Spread is performance and capacity. Fapoint-Spread can load millions of rows (dozens of columns per row) of data, but the performance is still very good. However, VBFlexGrid, when loading huge data sets, not only consumes more memory (and causes the computer to freeze), but also performs orders of magnitude slower. IMO, an important reason for this is that VBFlexGrid's data structures (such as TCOLS) are not optimized.

[Nouyana]

Added FlexDataSourceNoData enum.
This enables to keep the cell texts non-virtual and to make tool tip text virtual only. This allows to take advantage of a certain feature w/o the burden of full fletch virtual source.

Can you explain, what does it mean: "keep the cell texts non-virtual". For me it looks like literally "NoData":



In the cFlexDataSource class:

Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
   Dim RetVal As FlexDataSourceFlags
   If bKeepCellTextsNonVirtual Then
      RetVal = RetVal Or FlexDataSourceNoData
   Else
      RetVal = RetVal And Not FlexDataSourceNoData
   End If   
   IVBFlexDataSource2_GetFlags = RetVal
End Function

in the form:

Private Sub chkKeepCellTextsNonVirtual_Click()
   oDataSource.KeepCellTextsNonVirtual = CBool(chkKeepCellTextsNonVirtual)
   Set VBFlexGrid1.FlexDataSource = oDataSource
End Sub

[Krool]

Update released.

Memory consumption of TCELL struct reduced from 64 bytes to 52 bytes.

Can you explain, what does it mean: "keep the cell texts non-virtual". For me it looks like literally "NoData":

This means the normal .TextMatrix must be used to populate the cell texts. GetData/SetData is not used on IVBFlexDataSource.

[Nouyana]

This means the normal .TextMatrix must be used to populate the cell texts. GetData/SetData is not used on IVBFlexDataSource.

Oh, I understand. I can use virtual checkboxes and virtual ToolTips, but not GetData/SetData. Thank you!

[Nouyana]

Too much memory consumption when using the FlexDataSourceUnboundFixedColumns flag.

[Nouyana]

Update released.
Memory consumption of TCELL struct reduced from 64 bytes to 52 bytes.

Better results with v.1.6.5. The previous GIF was with v.1.6.0.

[Krool]

Too much memory consumption when using the FlexDataSourceUnboundFixedColumns flag.

It feels like that "empty column" have strings set? (As can be seen by the >)

[Krool]

It's very difficult to further reduce TCELL structure.. too many features
Also introducing a "simple mode" is not very easy to achieve w/o usage of conditional compilation. Beside the fact of the additional overhead in code. (Two structures side by side "if else" which to use)
So I keep things as there are...

Info: the grid can't use a simple 2D array. TCOLS has it's sense..
It's optimized for sorting rows.

[Nouyana]

It feels like that "empty column" have strings set? (As can be seen by the >)

Yes, but the only one cell.
By the way, what value it would be better to use when I leave the row? "" or vbNullString or what?

[Nouyana]

Also introducing a "simple mode" is not very easy to achieve w/o usage of conditional compilation.

Let it be a conditional compilation. Why not?

It's very difficult to further reduce TCELL structure.. too many features

Let's count a little. In v.1.6.0 it was 614 MB per 1 fixed column with 1 750 000 rows. In v.1.6.5 the same variable is 496 MB. So it was reduced 614-496=118 MB or 70 bytes per cell. It was not only about TCELL structure. And now we have another reason too, I'm sure. You have changed something else.

PS I'm an economist