Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist (1 Viewer)

If you can please post the adodb version.
Will do

Turns out I ported an earlier version that was much simpler - Before all the customer/orders data was in the db. Im porting this current version now and will post when ready.
 
Will do

Turns out I ported an earlier version that was much simpler - Before all the customer/orders data was in the db. Im porting this current version now and will post when ready.
Here is the ADODB version
There is one Folder that contains three Files in the Zip
1. TreeviewDemo.adp: MS 2007 ADP or AccessProject:
2. TreeviewDemo.bak: MSSQL 2012 Database backup File
3. qryToViews.sql: SQL file of the statements I used to convert the Access queries to Views

Not sure if I clicked every button and tested all the functionality but it seems to be working
Main Changes Needed
1. Replaced References from DAO to ADODB
2 Added a module DBFunctions which includes a set of functions for working with ADODB
3. Calls to Set mRecordSet = CurrentDb.OpenRecordset were replaced with
Set mRecordSet = DBFunctions.GetRecordSet
4. Calls to rs.FindFirst -> Replaced with rs.MoveFirst then rs.Find
5. Calls to re.NoMatch -> Replaced with rs.BOF
6. Calls to rs.FindNext -> Replaced with rs.Find
7. Bookmark Declaration changed to Variant
8. General Cleanup of table/field names - My DB is case sensitive so it barked at non-matching case

In most places I made changes I added a comment Line of ( 'FRL ) my initials so anyone could search on them.

Now I actually have to study the code so I know what I'm playing with.
Like to move from being a Plumber to a Designer

Have fun guys - Hope this helps anyone who plays with SQL in an ADP Project
 

Attachments

Here is an update to above Attachment - Found some areas of I had not updated - specifically the 2nd TreeForm which loads customers and orders.
Made the ports and cleaned up the query module.
 

Attachments

@MajP
Somethings not working right on the sort sequence #. The Level_ID is populating correctly but the sort order isn't. Thoughts just by looking at the output?

1586200693115.png
 
At quick glance, at the TreeView, at level 3, it appears that the sort order got reversed. I'm not sure if this is related or not.

So I'm going to resort them. Close the TreeView form.
1586202505456.png


Reopen the treeview. For the small group of 3 records that I resquenced, it's correct.

Maybe I did too many records in a single change and it got confused?

Somethings not working right on the sort sequence #
 
Thanks @MajP Going to use this for my next project which will be a Open Code Code Library, I use Total Visual SourceBook 2013 But never built one before :) , I should also be able to get the code looking like the VB editor,

Love the images that will make it look really cool Thanks

P.S. I will test it on my mrs 64 bit system.

keep safe mick
 
Sorting alphabetically by the looks of it?
Can you get rid of the leading L ?

That wouldn't stop it sorting alphabetically as the 'number parts' like 3.1.2 are not actually numbers
One way is to sort using a derived number column where e.g. L3.11.2 becomes 31102, L3.1.3 becomes 30103, L3.2=>30200 etc

NOTE I haven't tried any of this code & there may be a better way of sorting already built in ???
 
@Gasman and @isladogs.
Neither recommendation would be relevant here because it is using the tree position of the nodes as a means to establish a sort order. The Tree allows drag and drop and the ability to move nodes up and down within a level. In order to maintain that defined sort the tree view has an index property. If you read a fully expanded tree from the top down, the indices are defined in that order regardless of their level The code uses the tree node order not any kind of query sorting. It then saves this order to the table on close. This way when you reload the tree it maintains the desired order.
Node '1
----- Node '2
------------Node '3
----- Node '4
Node '5
In this class we also added a feature to Auto Outline. It thens adds the outline numbering once you move things up and down in the tree. But the sort and the numbering are somewhat independent.
 
Last edited:
Also look at the Pairing table. At first I did not think this was necessary to store the 3 keys (motherID, fatherID, and PairingID), but it had a lot of advantages IMO.

I know its ages since we spoke about this but I came across a discussion about pairing in a stack overflow discussion directly relevant to the above.

How do you constrain a database so there are only unique pairs in a database table?

If Kirk is friends with Victoria if we store that as a partnership we should not store Victoria as a partnership with Kirk.

From the thread we know that for any pairing the combination of the sum and the absolute difference will be unique so you can create an index on two computed columns
we know that A + B = B + A and we know that for absolute values A-B = B-A

we can make a computed column C1 of A+B and we can make a computed column C2 of ABS(A-B)
and then a unique constraint on C1,C2


For genetics maybe doesn't need to be reversable as mother could always be in one field and father in other...

I'm thinking this would be really good in systems where there was children and the parents or guardians may be two males / two females or guardian 1 and guardian 2 - so they are paired but the gender order of the pairing could vary although always unique irrespective of order.. and the indepotent nature of the constraint would mean that you could generate a pairing table from the primary keys of the guardians as you go along.
 
Last edited:
I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to span something like a file directory with an unknown amount of levels and branches. Common in doing things like Family trees and working with assemblies sub assemblies or tasks and sub tasks. Tree Views are a good way to show these Hierarchies. Think of an outline with multiple levels.

Recursion
So here is a simple example to demo recursion. The procedure is told how many times to loop itself and the counter is incremented each time the procedure is called. It simply adds 10 to the starting value each time it calls itself. Recursion always needs a way to check if it should call itself again or stop (does not call itself). Recursion if often very inefficient and resource heavy, but for certain data structure it may be the only way. (Note the below demo could obviously be done without recursion).
Code:
Public Sub RecursiveAdd10(HowManyTimes As Long, Optional Start As Long = 0, Optional Counter As Long = 0)
   If HowManyTimes = Counter Then
    Debug.Print "Final " & Start
    Exit Sub
   End If
   Debug.Print "Value " & Start & " Counter " & Counter
   Start = Start + 10
   Counter = Counter + 1
   RecursiveAdd10 HowManyTimes, Start, Counter
End Sub
test it
Code:
Public Sub TestRecursive()
  'add 10 5 times
  RecursiveAdd10 6, 50
End Sub
so the output is:

Value 50 Counter 0
Value 60 Counter 1
Value 70 Counter 2
Value 80 Counter 3
Value 90 Counter 4
Value 100 Counter 5
Final 110

Hierarchical data
When working with hierarchical data normally each record references a parentID in the same table. This is often called a "Self Referencing" table.

This would give the structure

Show table

To get this into a Tree View or display the hierarchy you start at the first Root node then recursively find the children. In the demo below I use a tree view to span the nodes to build the tree. To see how this is done look at the class module TreeViewForm.


Tree Views
Working with treeviews is pretty code heavy, but I have created a custom class to make this very easy. If you build a query precisely as instructed, you can then build a tree view with only a couple lines of code. The class also ties database information (Primary Keys) to the nodes making it much easier to work between the treeview and the data. The TreeView is "pseudo-bound" to the data. Read the instruction in the TreeViewForm class module.

I have included Multiple Demos showing different data structures and features. The E2E demo has about all the bells and whistles I added after working with DGreen on a specific solution. However to simply load your own tree view requires you to build a query and a single line of code.
1. Load from common query
2. Drag and drop of nodes and update database
3. Add, edit delete record in database and update tree
4. Delete, edit node and update database
5. Move node up and down in level and update sort order
6. Apply icons to specific records
7. dynamically load icons from folder at runtime
8 Right click on node with pop up command bar
9. Right click off node with different command bar
10. Expand and collapse tree and branch
11. Node selected to synch subform
12. Node double click to add, edit, delete
13. Auto level creates outline numbering
14. Save sort
15. Autolevel levels
16. Full load of nodes or light load and add nodes when expanded

Also included is a demo of Tree Views using MS Forms instead of the Active X.
The Span Files and Directory show a recursive span of a file system logging the information with another recursion to load the tree view.
I have also added to this capability with a class module for loading this Tree View.

View attachment 107131

Other Threads on TreeViews

Other Threads on general Recursion

Part Two to this thread focusing on recursion and shows how to build a simulated tree view with only a listbox.

Hi,

Thanks for so sharing such a great tool. I want to use tree view for one of my folder and used span folder option to populate the tblFileFolder. No of records in the table are 3000. I am then using TreeViewRecordLoader Class to generate my MS Forms tree view which add nodes from the table recursively due to which it takes about a minute or so and during which it seems that access got stuck. Is it possible to load the tree view faster instead of adding one by one node or is there any other way to speed up the tree view generation?

Best Regards
 
Did you look at this example?

The question is what is taking the time. Are you storing the file structure in a table then loading the tree or are you reading the files and loading the tree at the same time. Both of these take time.
In my example I read from the files and load the data to a table. Then I build the tree. I also do a lot of processing.
There may be more efficient ways to read and load the files to the table.
However once in a table I have the code in the class to do what I call a "light load". I only load the visible nodes. I think the demo has 10,000 nodes but I only load those needed for visibility. Maybe that is a a few hundred. Then when you click on a node it loads the next set of child nodes needed for visibility. This tree loads immediately and expands as you work with it.

If you want, I recommend starting a new thread which is easier and cleaner than adding to this long thread.
 
OK, now I have upgraded to 2019, I can try some your your examples. :)
I am getting a good few errors in the Span Files DB

I am trying it on my F:\Temp folder which has lots of files and folders beneath it.
I am getting errors as
1712490152912.png

Then
1712490191159.png

Then
1712490223830.png

then
1712490255836.png

then I just get an empty form.

What am I doing wrong please?
The other two db's appeard to work correctly?
1712490349157.png
 
The Tempquery was not deleted, and I should check that first which I was not doing.

Add the Following function
Code:
Public Function QueryExists(QueryName As String) As Boolean
  Dim qdf As QueryDef
  For Each qdf In CurrentDb.QueryDefs
   If qdf.Name = QueryName Then
     QueryExists = True
     Exit Function
   End If
  Next qdf
End Function

Then replace this or add the new line of code

Code:
Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  'Debug.Print strSql

  'Check to see if tempquery did not get deleted
  If QueryExists("tempQuery") Then CurrentDb.QueryDefs.Delete ("tempquery")
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function
 
I added that function and copied and pasted the second, left it to run, as there are a good few files in there.
Came back to
1712498554319.png

then I tried with a smaller folder and that worked.

Thank you.
 
VBA cannot handle all file and folder names that you can use. My guess there is some special character.

You could put an error handler
Code:
Private Sub SpanFolders(SourceFolderFullName As String, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
   ' Dim ParentID As Long

    
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
    
    FolderLevel = FolderLevel + 1
    LogFilesFolders SourceFolder.Name, SourceFolder.Path, SourceFolder.Type, ParentID, fft_Folder, FolderLevel
    ParentID = GetFolderID(SourceFolder.Path)
    For Each FileItem In SourceFolder.Files
        '2 and 4 are system and hidden files
        If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 Then
            LogFilesFolders FileItem.Name, FileItem.Path, FileItem.Type, ParentID, fft_File, FolderLevel
        End If
    Next FileItem
    
    For Each SubFolder In SourceFolder.SubFolders
        ParentID = GetFolderID(SourceFolder.Path) ' The record has just been added so get PK by name
    '   LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
       If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
            SpanFolders SubFolder.Path, ParentID, FolderLevel
       End If
    Next SubFolder

    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub

My guess is the sourcefolderfullname . In the error handler print that out.
 
That is not the code I have?
You already have an error handler section, so I added the SourceFolderFullName to the MSGBOX output.
This was the first offending file
1712508062850.png

Then I get almost immediately after trying to run it again.
1712516150036.png

and this in the debug window.
I will add the file name to the summation error message as well.
1054_FolderFiles current
1055_FolderFiles current
1056_FolderFiles current
1057_FolderFiles current
1058_FolderFiles current
1059_FolderFiles current
1060_FolderFiles current
1061_FolderFiles current
1062_FolderFiles current
1063_FolderFiles current
1064_FolderFiles current
1065_FolderFiles current
1066_FolderFiles current
1067_FolderFiles current
1068_FolderFiles current
1069_FolderFiles current
1070_FolderFiles current
1071_FolderFiles current
1072_FolderFiles current
1073_FolderFiles current
1074_FolderFiles current
1075_FolderFiles current
Edit: That is not the error being displayed from SpanFolderSummations, as I thought it might be?
 
Last edited:
The biggest feature I would still like to add is a "Light Load". Currently it loads all of the nodes on open. In this example no problem. I have a tree with 50K nodes and that can take a real long time. Ideally you would simply add the top level, and then when the user expands a node it only loads the next level for that node. Unfortunately there is a problem doing that. If you do not add the child node (at least 1) there is no plus symbol so you do not know to expand it. The trick I am thinking is adding the first child node per each node in the level. That one is going to take some thinking but I think it is doable. That is not specific to your case
For your's need to try to update the autolevel values on drag and drop without having to reload the whole tree.
Besides that I think you have covered about as many features as you can cram into a tree view.
I know this is an old thread but ..

If you just show the plus sign anyway, and a message saying "nothing to display" when clicked, would that be more irritating?

Alternatively could you trick the plus into only appearing where there is an expansion. You would only need to read a single node to check, if the required shallow depth had been reached. You could use "node exists" plus "shallow depth " reached as a trigger to end the recursion and just show the plus sign. Something like that?
 
@gemma-the-husky
Unfortunately, you did not see the threads below that thread. That problem was solved and incorporated into the class as a major feature. Immediately after that thread I followed with the solution and provided the code and the updated class for doing a light load. The class allows you to do a full or light load. This works great. It does this by adding a single child node under the visible nodes. Then when you expand it flushes out the current level and again adds a single child node to the visible node. In the demo there is a light load example with 10k nodes and loads instantaneously.

If you just show the plus sign anyway, and a message saying "nothing to display" when clicked, would that be more irritating?
That is not doable since there is no property to enforce the expansion sign. The expansion sign only exists if there is a child node. It is a function of the ActiveX control. You can do the opposite and there is a property not to show any expansion signs. I do not know the purpose of that. You have no idea what ones are expandable.

Alternatively could you trick the plus into only appearing where there is an expansion. You would only need to read a single node to check, if the required shallow depth had been reached. You could use "node exists" plus "shallow depth " reached as a trigger to end the recursion and just show the plus sign. Something like that
That is basically exactly what the code does. It only puts a single child node per visible node and only flushes it out when you expand. See post 112 and 113.
I did this for the ActiveX control and currently working to do it for the JKP control that uses MS Forms.
 

Users who are viewing this thread

Top Bottom