Help optimising a Tree view of large Database

[kamandoyle]

Hello guys and gals, first post here so be nice!

Right, I have a form with a Treeview in. I have it populated from a self-referencing database using the following code.

Private Sub Form_Load()
Const strTableQueryName = "SELECT * FROM tblHierarchy ORDER BY tblHierarchy.Function_Parent;"

Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)

AddBranch rst, "Function_Parent", "Function_Id", "Function_Name"
End Sub

'================= AddBranch Sub Procedure ======================
'      Recursive Procedure to add branches to TreeView Control
'Requires:
'   ActiveX Control:  TreeView Control
'              Name:  xTree
'Parameters:
'               rst:  Self-referencing Recordset containing the data
'   strPointerField:  Name of field pointing to parent's primary key
'        strIDField:  Name of parent's primary key field
'      strNameField:  Name of field containing text to be displayed
'=============================================================
Sub AddBranch(rst As Recordset, strPointerField As String, _
              strIDField As String, strNameField As String, _
              Optional varParentID As Variant)
   On Error GoTo errAddBranch
   Dim nodCurrent As Node, objTree As TreeView
   Dim strCriteria As String, strText As String, strKey As String
   Dim nodParent As Node, bk As String
   Set objTree = Me!xTree.Object
   If IsMissing(varParentID) Then  ' Root Branch.
      strCriteria = strPointerField & " Is Null"
   Else  ' Search for records pointing to parent.
      strCriteria = BuildCriteria(strPointerField, _
           rst.Fields(strPointerField).Type, "=" & varParentID)
      Set nodParent = objTree.Nodes("a" & varParentID)
   End If

      ' Find the first component to report to the rig node.
   rst.FindFirst strCriteria
   Do Until rst.NoMatch
         ' Create a string with Name
      strText = rst(strIDField) & " - " & rst(strNameField)
      strKey = "a" & rst(strIDField)
      If Not IsMissing(varParentID) Then  'add new node to the parent
         Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText)
      Else    ' Add new node to the root.
         Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
      End If
         ' Save your place in the recordset so we can pass by ref for speed.
      bk = rst.Bookmark
         ' Add components that belong to this node.
      AddBranch rst, strPointerField, strIDField, strNameField, rst(strIDField)
      rst.Bookmark = bk     ' Return to last place and continue search.
      rst.FindNext strCriteria   ' Find next component.
   Loop

exitAddBranch:
   Exit Sub

      '--------------------------Error Trapping --------------------------
errAddBranch:
   MsgBox "Can't add child:  " & Err.Description & " Key: " & strKey & " Text:" & strText, vbCritical, "AddBranch Error:"
   Resume exitAddBranch
End Sub

The database this is referencing is about 30000 lines and it takes ~4 minutes to populate this way. I know Treeview isn't really supposed to be used in this way however it's what is required.

Now I have come up with the theory that I will populate each node with children as its clicked to be expanded. This in theory should drive down loading times as it's only populating what is being looked at, I however have no idea how to do this. I have searched Google extensively with no avail so ANY help what so ever would be a brilliant help.

Thanks in advance!

EDIT: Uploaded my current treeview example

 

[spikepl]

I had q uick look:

1. Did you set index on all the relevant fields? It does not seem to me that way
2. AFAIK .Seek method can make use of an index, whereas .Find cannot. Your code depends heavily on looping and .Find so that is bound to take long time. You could open the table directly and have dbOpenTable instead of dbOpenDynaset and then you could use .Seek.

Before you make any major changes, I'd suggest to work on a copy of the db with all the data local to the db (i.e. not split) and test how .Seek performs.

 

[Rx_]

For those of you trying to open this, the reference to the OCX will fail.

Don't suppose by chance this Treview OCX is available for free download?
The Access Developers at Access really need to walk down the hall to some C++ programmers and obtain a new improved Tree view for Access. I would even buy a round of beers for that.

 

[MarkK]

You need three pieces, minimum, of data at node creation, ID, Text, and ChildCount, which indicates whether the current node has children. Create a recordset that does that FOR ONE BRANCH. ID and Text are obvious, but if ChildCount > 0, then add a dummy node under that parent, something like this . . .

set nc = m_tree.nodes.add(np.key, tvwChild, rst!ID, rst!text)
if rst!ChildCount > 0 then
   m_tree.nodes.add nc.key, tvwChild, "~" & rst!ID[COLOR="Green"] 'note the squiggle in the key[/COLOR]
end if

See what happens there? That ensures that each node that has children has a plus sign in front, and is capable of firing an Expand event.

Then you handle expand and look for your dummy node with the ~ "squiggle" in the key . . .

private sub m_tree_expand(Node as MSComCtlLib.Node)
   If Node.Children = 1 Then
      If Left(Node.Child.Key, 1) = "~" Then
[COLOR="Green"]         'this is one of our dummy nodes, so remove it[/COLOR]
         m_tree.Nodes.Remove Node.Child.index
         [COLOR="Green"]'and use the expanding node to extract parent ID  [/COLOR]
         CreateBranch Node
      end if
   end if
end sub

And that's it. Rather than have your node creation routine automatically recur until all nodes are created, we interrupt that recursion until we detect an expand event, and use that as a trigger for the next branch of node creation.

Also, the signature I would use for a node create routine is . . .

CreateBranch(NodeParent as MSComCtlLib.Node)

. . . so that requires that you put enough information in each node--key or tag or whatever--that allows you to open the recordset you need to populate that node's children.

Hope this helps,

 

[kamandoyle]

Cheers guys, your help is very much appreciated.