Improving treeview load time

K

Ken Warthen

I have an ActiveX treeview control on a form in an Access 2007 database. The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour. If anyone out there is well versed in this control,
I'd be very appreciative for any advise on how to improve the load time with
this control. My code follows.

TIA,

Ken

Private Sub Form_Load()
On Error GoTo PROC_ERROR
Dim rsFSMG As DAO.Recordset
Dim rsRegion As DAO.Recordset
Dim rsOwner As DAO.Recordset
Dim rsSalesCenter As DAO.Recordset
Dim rsCustomer As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim tvw As TreeView
Dim strNodeName As String
Dim strRegion As String
Dim strOwner As String
Dim strSalesCenter As String
Dim strCustomer As String
Dim theNode As Node
Dim theRegion As Node
Dim theOwner As Node
Dim theSalesCenter As Node
Dim theCustomer As Node


DoCmd.OpenForm "frmLoadingFormMessage"
DoEvents
Me.visible = False

'load the treeview control

Set tvw = Me.tvwNodes.Object
tvw.Nodes.Clear

'Level 1 — FSMG Regions
strSQL1 = "SELECT DISTINCT [FSMGRegionID],[FSMGRegion] FROM tblMain"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
With rsFSMG
.MoveFirst
Do Until rsFSMG.EOF
strNodeName = rsFSMG.Fields("FSMGRegion")
Set theNode = Me.tvwNodes.Nodes.Add(, , , strNodeName)
'Level 2 — Regions
strSQL2 = "SELECT DISTINCT [Region] FROM tblMain WHERE
[FSMGRegion] ='" & strNodeName & "'"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
With rsRegion
.MoveFirst
Do Until rsRegion.EOF
strRegion = .Fields("Region")
Set theRegion = Me.tvwNodes.Nodes.Add(theNode,
tvwChild, , strRegion)
'Level 3 — Owner
strSQL3 = "SELECT DISTINCT [Owner] FROM tblMain
WHERE [Region]='" & strRegion & "'"
Set rsOwner = CurrentDb.OpenRecordset(strSQL3)
With rsOwner
.MoveFirst
Do Until .EOF
strOwner = .Fields("Owner")
Set theOwner =
Me.tvwNodes.Nodes.Add(theRegion, tvwChild, , strOwner)
'Level 4 — Sales Centers/Branch
strSQL4 = "SELECT DISTINCT [SalesCenter]
FROM tblMain WHERE [Owner]='" & strOwner & "'"
Set rsSalesCenter =
CurrentDb.OpenRecordset(strSQL4)
With rsSalesCenter
.MoveFirst
Do Until .EOF
strSalesCenter =
..Fields("SalesCenter")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theOwner, tvwChild, , strCustomer)
'Level 5 — Customer/Account
strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strCustomer & "'"
Set rsCustomer =
CurrentDb.OpenRecordset(strSQL5)
With rsCustomer
.MoveFirst
Do Until .EOF
strCustomer =
..Fields("Customer")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theCustomer, tvwChild, , strCustomer)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
rsFSMG.Close
rsRegion.Close
rsOwner.Close
rsCustomer.Close
rsCustomer.Close
Set rsFSMG = Nothing
Set rsRegion = Nothing
Set rsOwner = Nothing
Set rsCustomer = Nothing
Set rsCustomer = Nothing
Me.visible = True
DoCmd.Close acForm, "frmLoadingFormMessage"



PROC_EXIT:
Exit Sub
PROC_ERROR:
Call ShowError("frmViewPromotions", "Form_Load", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Sub
 
S

Stefan Hoffmann

hi Ken,

Ken said:
I have an ActiveX treeview control on a form in an Access 2007 database. The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour. If anyone out there is well versed in this control,
I'd be very appreciative for any advise on how to improve the load time with
this control.
Click to expand...
The bottle neck arises from the multiple recordsets opened in the inner
loops.

The easiest optimization: Open the recordsets for each level without
filter outside of your loop. Use FindFirst, FindNext to locate the inner
records.



mfG
--> stefan <--
 
S

Stefan Hoffmann

hi,

Stefan said:
The easiest optimization: Open the recordsets for each level without
filter outside of your loop. Use FindFirst, FindNext to locate the inner
records.
Click to expand...
Oops, nonsens.

The nodes collection is flat, so use good naming scheme for the node key
then you can simply add all nodes without loop nesting:

yourTreeView.Nodes.Add ParentKey, _
tvwChild, _
ChildKey, _
Label

If you compose the key as a path then it is simple, e.g.

Do While Not rsLevel1.Eof
yourTreeView.Nodes.Add , , "L1ID" & rsLevel1![ID], rsLevel1![Label]
rsLevel1.MoveNext
Loop

Do While Not rsLevel2.Eof
yourTreeView.Nodes.Add "L1ID" & rsLevel2![IDParent], _
tvwChild, _
"L2ID" & rsLevel2![ID], _
rsLevel2![Label]
rsLevel2.MoveNext
Loop

...

mfG
--> stefan <--
 
S

Stefan Hoffmann

hi,

Stefan said:
The nodes collection is flat, so use good naming scheme for the node key
then you can simply add all nodes without loop nesting:
Click to expand...
I hope you understand the idea, my English is terrible today.
If you compose the key as a path then it is simple, e.g.
Click to expand...
While you can use an artificial path, I used a simple identity in the
node key. This means, if the data has such a simple ID on each level you
should give it a try.

Otherwise, using a more complex path-like key may be the better choice.


mfG
--> stefan <--
 
P

Paolo

Hi Ken Warthen,
for speeding up Stefan Hoffmann already gave you a good suggestion, but it
seems to me that in your code something is wrong i.e.

**** Set theCustomer =
Me.tvwNodes.Nodes.Add(theOwner, tvwChild, , strCustomer)****
I think it must be
Set theSalesCenter = Me.tvwNodes.Nodes.Add(theOwner, tvwChild, ,
strSalesCenter)
'Level 5 — Customer/Account
**** strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strCustomer & "'" *****
I think it must be
strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strSalesCenter & "'"

HTH Paolo

Ken Warthen said:
I have an ActiveX treeview control on a form in an Access 2007 database. The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour. If anyone out there is well versed in this control,
I'd be very appreciative for any advise on how to improve the load time with
this control. My code follows.

TIA,

Ken

Private Sub Form_Load()
On Error GoTo PROC_ERROR
Dim rsFSMG As DAO.Recordset
Dim rsRegion As DAO.Recordset
Dim rsOwner As DAO.Recordset
Dim rsSalesCenter As DAO.Recordset
Dim rsCustomer As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim tvw As TreeView
Dim strNodeName As String
Dim strRegion As String
Dim strOwner As String
Dim strSalesCenter As String
Dim strCustomer As String
Dim theNode As Node
Dim theRegion As Node
Dim theOwner As Node
Dim theSalesCenter As Node
Dim theCustomer As Node


DoCmd.OpenForm "frmLoadingFormMessage"
DoEvents
Me.visible = False

'load the treeview control

Set tvw = Me.tvwNodes.Object
tvw.Nodes.Clear

'Level 1 — FSMG Regions
strSQL1 = "SELECT DISTINCT [FSMGRegionID],[FSMGRegion] FROM tblMain"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
With rsFSMG
.MoveFirst
Do Until rsFSMG.EOF
strNodeName = rsFSMG.Fields("FSMGRegion")
Set theNode = Me.tvwNodes.Nodes.Add(, , , strNodeName)
'Level 2 — Regions
strSQL2 = "SELECT DISTINCT [Region] FROM tblMain WHERE
[FSMGRegion] ='" & strNodeName & "'"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
With rsRegion
.MoveFirst
Do Until rsRegion.EOF
strRegion = .Fields("Region")
Set theRegion = Me.tvwNodes.Nodes.Add(theNode,
tvwChild, , strRegion)
'Level 3 — Owner
strSQL3 = "SELECT DISTINCT [Owner] FROM tblMain
WHERE [Region]='" & strRegion & "'"
Set rsOwner = CurrentDb.OpenRecordset(strSQL3)
With rsOwner
.MoveFirst
Do Until .EOF
strOwner = .Fields("Owner")
Set theOwner =
Me.tvwNodes.Nodes.Add(theRegion, tvwChild, , strOwner)
'Level 4 — Sales Centers/Branch
strSQL4 = "SELECT DISTINCT [SalesCenter]
FROM tblMain WHERE [Owner]='" & strOwner & "'"
Set rsSalesCenter =
CurrentDb.OpenRecordset(strSQL4)
With rsSalesCenter
.MoveFirst
Do Until .EOF
strSalesCenter =
.Fields("SalesCenter")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theOwner, tvwChild, , strCustomer)
'Level 5 — Customer/Account
strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strCustomer & "'"
Set rsCustomer =
CurrentDb.OpenRecordset(strSQL5)
With rsCustomer
.MoveFirst
Do Until .EOF
strCustomer =
.Fields("Customer")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theCustomer, tvwChild, , strCustomer)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
rsFSMG.Close
rsRegion.Close
rsOwner.Close
rsCustomer.Close
rsCustomer.Close
Set rsFSMG = Nothing
Set rsRegion = Nothing
Set rsOwner = Nothing
Set rsCustomer = Nothing
Set rsCustomer = Nothing
Me.visible = True
DoCmd.Close acForm, "frmLoadingFormMessage"



PROC_EXIT:
Exit Sub
PROC_ERROR:
Call ShowError("frmViewPromotions", "Form_Load", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Sub
Click to expand...
 
K

Ken Warthen

Stefan,

Thanks for the help. I'm not quite sure what you mean by composing the key
as a path.

Ken

Stefan Hoffmann said:
hi,

Stefan said:
The easiest optimization: Open the recordsets for each level without
filter outside of your loop. Use FindFirst, FindNext to locate the inner
records.
Click to expand...
Oops, nonsens.

The nodes collection is flat, so use good naming scheme for the node key
then you can simply add all nodes without loop nesting:

yourTreeView.Nodes.Add ParentKey, _
tvwChild, _
ChildKey, _
Label

If you compose the key as a path then it is simple, e.g.

Do While Not rsLevel1.Eof
yourTreeView.Nodes.Add , , "L1ID" & rsLevel1![ID], rsLevel1![Label]
rsLevel1.MoveNext
Loop

Do While Not rsLevel2.Eof
yourTreeView.Nodes.Add "L1ID" & rsLevel2![IDParent], _
tvwChild, _
"L2ID" & rsLevel2![ID], _
rsLevel2![Label]
rsLevel2.MoveNext
Loop

...

mfG
--> stefan <--
Click to expand...
 
S

Stefan Hoffmann

hi Ken,

E.g. like this:

strSQL1 = "SELECT [FSMGRegionID],[FSMGRegion] " & _
"FROM tblMain " & _
"GROUP BY [FSMGRegionID],[FSMGRegion]"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
Do While Not rsFSMG.Eof
vwNodes.Nodes.Add , , "L1=" & rsFSMG![FSMGRegionID], strNodeName
rsFSMG.MoveNext
Loop
rsFSMG.Close
Set rsFSMG = Nothing

strSQL2 = "SELECT [FSMGRegionID], [Region] " & _
"FROM tblMain " & _
"GROUP BY [FSMGRegionID], [Region]"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
Do While Not rsRegion .Eof
vwNodes.Nodes.Add "L1=" & rsFSMG![FSMGRegionID], _
tvwChild, _
"L1=" & rsFSMG![FSMGRegionID] & _
"+L2=" & rsRegion![Region], _
rsRegion![Region]
rsRegion.MoveNext
Loop
rsRegion.Close
Set rsRegion= Nothing


mfG
--> stefan <--
 
K

Ken Warthen

I've been playing with this for a few hours and don't seem any closer to a
solution. The following code will load the first level into the treeview but
then generates an error when it attempts to load the second level. I'm
getting the "Object variable or with block variable not set" error with the
following code on the level two me.tvwNodes.nodes.add... line. - Ken

Private Sub Form_Load()
On Error GoTo PROC_ERROR
Dim tvw As MSComctlLib.TreeView
Dim rsLevel1 As DAO.Recordset
Dim rsLevel2 As DAO.Recordset
Dim rsLevel3 As DAO.Recordset
Dim rsLevel4 As DAO.Recordset
Dim rsLevel5 As DAO.Recordset
Dim rsLevel6 As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim strSQL6 As String

Set tvw = Me.tvwNodes.Object
tvw.Nodes.Clear

'// Level 1 — Plants
strSQL1 = "SELECT DISTINCT tblPowerade32oz.PlantID,
tblPowerade32oz.Plant " & _
"FROM tblPowerade32oz " & _
"WHERE (((tblPowerade32oz.PlantID) Is Not Null)) ORDER BY
tblPowerade32oz.Plant;"
Set rsLevel1 = CurrentDb.OpenRecordset(strSQL1)
With rsLevel1
Do While Not .EOF
Me.tvwNodes.Nodes.Add Key:="L1" & rsLevel1![PlantID],
Text:=rsLevel1![Plant]
.MoveNext
Loop
End With
rsLevel1.Close
Set rsLevel1 = Nothing

'// Level 2 - FSMG Regions
strSQL2 = "SELECT DISTINCT tblPowerade32oz.FSMGRegion,
tblPowerade32oz.PlantID, tblPowerade32oz.Plant " & _
"FROM tblPowerade32oz " & _
"GROUP BY tblPowerade32oz.FSMGRegion, tblPowerade32oz.PlantID,
tblPowerade32oz.Plant " & _
"HAVING (((tblPowerade32oz.PlantID) Is Not Null));"

Set rsLevel2 = CurrentDb.OpenRecordset(strSQL2)
With rsLevel2
Do While Not .EOF
Me.tvwNodes.Nodes.Add "L1=" & rsLevel1![PlantID], tvwChild,
"L1=" & rsLevel1![PlantID] & "+L2=" & rsLevel2![FSMGRegion],
rsLevel2![FSMGRegion]
.MoveNext
Loop
End With
rsLevel2.Close
Set rsLevel2 = Nothing

PROC_EXIT:
Exit Sub
PROC_ERROR:
Call ShowError("frmViewPromotions", "Form_Load", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Sub





Stefan Hoffmann said:
hi Ken,

E.g. like this:

strSQL1 = "SELECT [FSMGRegionID],[FSMGRegion] " & _
"FROM tblMain " & _
"GROUP BY [FSMGRegionID],[FSMGRegion]"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
Do While Not rsFSMG.Eof
vwNodes.Nodes.Add , , "L1=" & rsFSMG![FSMGRegionID], strNodeName
rsFSMG.MoveNext
Loop
rsFSMG.Close
Set rsFSMG = Nothing

strSQL2 = "SELECT [FSMGRegionID], [Region] " & _
"FROM tblMain " & _
"GROUP BY [FSMGRegionID], [Region]"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
Do While Not rsRegion .Eof
vwNodes.Nodes.Add "L1=" & rsFSMG![FSMGRegionID], _
tvwChild, _
"L1=" & rsFSMG![FSMGRegionID] & _
"+L2=" & rsRegion![Region], _
rsRegion![Region]
rsRegion.MoveNext
Loop
rsRegion.Close
Set rsRegion= Nothing


mfG
--> stefan <--
Click to expand...
 
S

Stefan Hoffmann

hi Ken,

Ken said:
I've been playing with this for a few hours and don't seem any closer to a
solution. The following code will load the first level into the treeview but
then generates an error when it attempts to load the second level. I'm
getting the "Object variable or with block variable not set" error with the
following code on the level two me.tvwNodes.nodes.add... line. - Ken
Click to expand...
Your error is in the second loop.
With rsLevel2
Do While Not .EOF
Me.tvwNodes.Nodes.Add "L1=" & rsLevel1![PlantID], tvwChild,
"L1=" & rsLevel1![PlantID] & "+L2=" & rsLevel2![FSMGRegion],
rsLevel2![FSMGRegion]
.MoveNext
Loop
Click to expand...
You need to build the parent key from rsLevel2. Maybe only a typo.

mfG
--> stefan <--
 
T

Tony Toews [MVP]

Ken Warthen said:
I have an ActiveX treeview control on a form in an Access 2007 database. The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour.
Click to expand...

Somewhere, somewhen I saw a posting suggesting that you fill in the
grand child nodes only when the user has clicked on the main levels.
You fill in the child nodes as you fill in the main level so the +
works properly. This was on the NodeClick event.

Now the above is my vague memory so I could be quite wrong.

The following are some steps to try to speed up deleting treeviews
with lots of nodes. These may or may not be applicable.

nodes.clear can get real slow when dealing with lots of nodes. Try
turning the form's visibility off, do the clear method and turn the
form's visibility back on. Or possibly the painting property.

If you turn the treecontrol's visibility off, Access will GPF. (Not
sure what version of Access or the Treeview control)

Tom Mapp said "I just set the root node expansion to false, prior to
clearing.".

Tony
 
D

David H

You're working with 4,000 records. Its going to take time.

1) What specifically are you showing in the TreeView?

2) I would honestly go with a load on demand approach where child nodes are
added the first time that the user clicks on the parent using the NodeClick
event. The child nodes will remain in the TreeView until the form is closed.

3) Cascading combobox are a viable alternative although they obviously don't
provide the nice and slick look & feel of the treeview.


Ken Warthen said:
I have an ActiveX treeview control on a form in an Access 2007 database. The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour. If anyone out there is well versed in this control,
I'd be very appreciative for any advise on how to improve the load time with
this control. My code follows.

TIA,

Ken

Private Sub Form_Load()
On Error GoTo PROC_ERROR
Dim rsFSMG As DAO.Recordset
Dim rsRegion As DAO.Recordset
Dim rsOwner As DAO.Recordset
Dim rsSalesCenter As DAO.Recordset
Dim rsCustomer As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim tvw As TreeView
Dim strNodeName As String
Dim strRegion As String
Dim strOwner As String
Dim strSalesCenter As String
Dim strCustomer As String
Dim theNode As Node
Dim theRegion As Node
Dim theOwner As Node
Dim theSalesCenter As Node
Dim theCustomer As Node


DoCmd.OpenForm "frmLoadingFormMessage"
DoEvents
Me.visible = False

'load the treeview control

Set tvw = Me.tvwNodes.Object
tvw.Nodes.Clear

'Level 1 — FSMG Regions
strSQL1 = "SELECT DISTINCT [FSMGRegionID],[FSMGRegion] FROM tblMain"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
With rsFSMG
.MoveFirst
Do Until rsFSMG.EOF
strNodeName = rsFSMG.Fields("FSMGRegion")
Set theNode = Me.tvwNodes.Nodes.Add(, , , strNodeName)
'Level 2 — Regions
strSQL2 = "SELECT DISTINCT [Region] FROM tblMain WHERE
[FSMGRegion] ='" & strNodeName & "'"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
With rsRegion
.MoveFirst
Do Until rsRegion.EOF
strRegion = .Fields("Region")
Set theRegion = Me.tvwNodes.Nodes.Add(theNode,
tvwChild, , strRegion)
'Level 3 — Owner
strSQL3 = "SELECT DISTINCT [Owner] FROM tblMain
WHERE [Region]='" & strRegion & "'"
Set rsOwner = CurrentDb.OpenRecordset(strSQL3)
With rsOwner
.MoveFirst
Do Until .EOF
strOwner = .Fields("Owner")
Set theOwner =
Me.tvwNodes.Nodes.Add(theRegion, tvwChild, , strOwner)
'Level 4 — Sales Centers/Branch
strSQL4 = "SELECT DISTINCT [SalesCenter]
FROM tblMain WHERE [Owner]='" & strOwner & "'"
Set rsSalesCenter =
CurrentDb.OpenRecordset(strSQL4)
With rsSalesCenter
.MoveFirst
Do Until .EOF
strSalesCenter =
.Fields("SalesCenter")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theOwner, tvwChild, , strCustomer)
'Level 5 — Customer/Account
strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strCustomer & "'"
Set rsCustomer =
CurrentDb.OpenRecordset(strSQL5)
With rsCustomer
.MoveFirst
Do Until .EOF
strCustomer =
.Fields("Customer")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theCustomer, tvwChild, , strCustomer)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
rsFSMG.Close
rsRegion.Close
rsOwner.Close
rsCustomer.Close
rsCustomer.Close
Set rsFSMG = Nothing
Set rsRegion = Nothing
Set rsOwner = Nothing
Set rsCustomer = Nothing
Set rsCustomer = Nothing
Me.visible = True
DoCmd.Close acForm, "frmLoadingFormMessage"



PROC_EXIT:
Exit Sub
PROC_ERROR:
Call ShowError("frmViewPromotions", "Form_Load", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Sub
Click to expand...
 
D

David H

The example here is ASP.NET but does serve as valid proof of concept of load
I have an ActiveX treeview control on a form in an Access 2007 database. The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour. If anyone out there is well versed in this control,
I'd be very appreciative for any advise on how to improve the load time with
this control. My code follows.

TIA,

Ken

Private Sub Form_Load()
On Error GoTo PROC_ERROR
Dim rsFSMG As DAO.Recordset
Dim rsRegion As DAO.Recordset
Dim rsOwner As DAO.Recordset
Dim rsSalesCenter As DAO.Recordset
Dim rsCustomer As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim tvw As TreeView
Dim strNodeName As String
Dim strRegion As String
Dim strOwner As String
Dim strSalesCenter As String
Dim strCustomer As String
Dim theNode As Node
Dim theRegion As Node
Dim theOwner As Node
Dim theSalesCenter As Node
Dim theCustomer As Node


DoCmd.OpenForm "frmLoadingFormMessage"
DoEvents
Me.visible = False

'load the treeview control

Set tvw = Me.tvwNodes.Object
tvw.Nodes.Clear

'Level 1 — FSMG Regions
strSQL1 = "SELECT DISTINCT [FSMGRegionID],[FSMGRegion] FROM tblMain"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
With rsFSMG
.MoveFirst
Do Until rsFSMG.EOF
strNodeName = rsFSMG.Fields("FSMGRegion")
Set theNode = Me.tvwNodes.Nodes.Add(, , , strNodeName)
'Level 2 — Regions
strSQL2 = "SELECT DISTINCT [Region] FROM tblMain WHERE
[FSMGRegion] ='" & strNodeName & "'"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
With rsRegion
.MoveFirst
Do Until rsRegion.EOF
strRegion = .Fields("Region")
Set theRegion = Me.tvwNodes.Nodes.Add(theNode,
tvwChild, , strRegion)
'Level 3 — Owner
strSQL3 = "SELECT DISTINCT [Owner] FROM tblMain
WHERE [Region]='" & strRegion & "'"
Set rsOwner = CurrentDb.OpenRecordset(strSQL3)
With rsOwner
.MoveFirst
Do Until .EOF
strOwner = .Fields("Owner")
Set theOwner =
Me.tvwNodes.Nodes.Add(theRegion, tvwChild, , strOwner)
'Level 4 — Sales Centers/Branch
strSQL4 = "SELECT DISTINCT [SalesCenter]
FROM tblMain WHERE [Owner]='" & strOwner & "'"
Set rsSalesCenter =
CurrentDb.OpenRecordset(strSQL4)
With rsSalesCenter
.MoveFirst
Do Until .EOF
strSalesCenter =
.Fields("SalesCenter")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theOwner, tvwChild, , strCustomer)
'Level 5 — Customer/Account
strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strCustomer & "'"
Set rsCustomer =
CurrentDb.OpenRecordset(strSQL5)
With rsCustomer
.MoveFirst
Do Until .EOF
strCustomer =
.Fields("Customer")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theCustomer, tvwChild, , strCustomer)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
rsFSMG.Close
rsRegion.Close
rsOwner.Close
rsCustomer.Close
rsCustomer.Close
Set rsFSMG = Nothing
Set rsRegion = Nothing
Set rsOwner = Nothing
Set rsCustomer = Nothing
Set rsCustomer = Nothing
Me.visible = True
DoCmd.Close acForm, "frmLoadingFormMessage"



PROC_EXIT:
Exit Sub
PROC_ERROR:
Call ShowError("frmViewPromotions", "Form_Load", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Sub
Click to expand...
 
D

David H

Also depending on how the data is structured, a Tab Control with individual
TreeViews might be an alternative also using the load on demand approach. For
example, if the data represents clients in North America, Europe and Asia,
you'd have one tab for each individual Region with the region (ie as the top
most node of the tree view).

Ken Warthen said:
I have an ActiveX treeview control on a form in an Access 2007 database. The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour. If anyone out there is well versed in this control,
I'd be very appreciative for any advise on how to improve the load time with
this control. My code follows.

TIA,

Ken

Private Sub Form_Load()
On Error GoTo PROC_ERROR
Dim rsFSMG As DAO.Recordset
Dim rsRegion As DAO.Recordset
Dim rsOwner As DAO.Recordset
Dim rsSalesCenter As DAO.Recordset
Dim rsCustomer As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim tvw As TreeView
Dim strNodeName As String
Dim strRegion As String
Dim strOwner As String
Dim strSalesCenter As String
Dim strCustomer As String
Dim theNode As Node
Dim theRegion As Node
Dim theOwner As Node
Dim theSalesCenter As Node
Dim theCustomer As Node


DoCmd.OpenForm "frmLoadingFormMessage"
DoEvents
Me.visible = False

'load the treeview control

Set tvw = Me.tvwNodes.Object
tvw.Nodes.Clear

'Level 1 — FSMG Regions
strSQL1 = "SELECT DISTINCT [FSMGRegionID],[FSMGRegion] FROM tblMain"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
With rsFSMG
.MoveFirst
Do Until rsFSMG.EOF
strNodeName = rsFSMG.Fields("FSMGRegion")
Set theNode = Me.tvwNodes.Nodes.Add(, , , strNodeName)
'Level 2 — Regions
strSQL2 = "SELECT DISTINCT [Region] FROM tblMain WHERE
[FSMGRegion] ='" & strNodeName & "'"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
With rsRegion
.MoveFirst
Do Until rsRegion.EOF
strRegion = .Fields("Region")
Set theRegion = Me.tvwNodes.Nodes.Add(theNode,
tvwChild, , strRegion)
'Level 3 — Owner
strSQL3 = "SELECT DISTINCT [Owner] FROM tblMain
WHERE [Region]='" & strRegion & "'"
Set rsOwner = CurrentDb.OpenRecordset(strSQL3)
With rsOwner
.MoveFirst
Do Until .EOF
strOwner = .Fields("Owner")
Set theOwner =
Me.tvwNodes.Nodes.Add(theRegion, tvwChild, , strOwner)
'Level 4 — Sales Centers/Branch
strSQL4 = "SELECT DISTINCT [SalesCenter]
FROM tblMain WHERE [Owner]='" & strOwner & "'"
Set rsSalesCenter =
CurrentDb.OpenRecordset(strSQL4)
With rsSalesCenter
.MoveFirst
Do Until .EOF
strSalesCenter =
.Fields("SalesCenter")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theOwner, tvwChild, , strCustomer)
'Level 5 — Customer/Account
strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strCustomer & "'"
Set rsCustomer =
CurrentDb.OpenRecordset(strSQL5)
With rsCustomer
.MoveFirst
Do Until .EOF
strCustomer =
.Fields("Customer")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theCustomer, tvwChild, , strCustomer)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
rsFSMG.Close
rsRegion.Close
rsOwner.Close
rsCustomer.Close
rsCustomer.Close
Set rsFSMG = Nothing
Set rsRegion = Nothing
Set rsOwner = Nothing
Set rsCustomer = Nothing
Set rsCustomer = Nothing
Me.visible = True
DoCmd.Close acForm, "frmLoadingFormMessage"



PROC_EXIT:
Exit Sub
PROC_ERROR:
Call ShowError("frmViewPromotions", "Form_Load", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Sub
Click to expand...
 
A

Alex Dybenko

Hi,
Here a sample how to load on demand
http://www.pointltd.com/Downloads/Details.asp?dlID=36

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Ken Warthen said:
I have an ActiveX treeview control on a form in an Access 2007 database.
The
treeview loads data from a table of roughly 4,000 records. There are six
levels in the treeview. It takes an incredible amount of time to load the
data; about a half hour. If anyone out there is well versed in this
control,
I'd be very appreciative for any advise on how to improve the load time
with
this control. My code follows.

TIA,

Ken

Private Sub Form_Load()
On Error GoTo PROC_ERROR
Dim rsFSMG As DAO.Recordset
Dim rsRegion As DAO.Recordset
Dim rsOwner As DAO.Recordset
Dim rsSalesCenter As DAO.Recordset
Dim rsCustomer As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim tvw As TreeView
Dim strNodeName As String
Dim strRegion As String
Dim strOwner As String
Dim strSalesCenter As String
Dim strCustomer As String
Dim theNode As Node
Dim theRegion As Node
Dim theOwner As Node
Dim theSalesCenter As Node
Dim theCustomer As Node


DoCmd.OpenForm "frmLoadingFormMessage"
DoEvents
Me.visible = False

'load the treeview control

Set tvw = Me.tvwNodes.Object
tvw.Nodes.Clear

'Level 1 — FSMG Regions
strSQL1 = "SELECT DISTINCT [FSMGRegionID],[FSMGRegion] FROM tblMain"
Set rsFSMG = CurrentDb.OpenRecordset(strSQL1)
With rsFSMG
.MoveFirst
Do Until rsFSMG.EOF
strNodeName = rsFSMG.Fields("FSMGRegion")
Set theNode = Me.tvwNodes.Nodes.Add(, , , strNodeName)
'Level 2 — Regions
strSQL2 = "SELECT DISTINCT [Region] FROM tblMain WHERE
[FSMGRegion] ='" & strNodeName & "'"
Set rsRegion = CurrentDb.OpenRecordset(strSQL2)
With rsRegion
.MoveFirst
Do Until rsRegion.EOF
strRegion = .Fields("Region")
Set theRegion = Me.tvwNodes.Nodes.Add(theNode,
tvwChild, , strRegion)
'Level 3 — Owner
strSQL3 = "SELECT DISTINCT [Owner] FROM tblMain
WHERE [Region]='" & strRegion & "'"
Set rsOwner = CurrentDb.OpenRecordset(strSQL3)
With rsOwner
.MoveFirst
Do Until .EOF
strOwner = .Fields("Owner")
Set theOwner =
Me.tvwNodes.Nodes.Add(theRegion, tvwChild, , strOwner)
'Level 4 — Sales Centers/Branch
strSQL4 = "SELECT DISTINCT
[SalesCenter]
FROM tblMain WHERE [Owner]='" & strOwner & "'"
Set rsSalesCenter =
CurrentDb.OpenRecordset(strSQL4)
With rsSalesCenter
.MoveFirst
Do Until .EOF
strSalesCenter =
.Fields("SalesCenter")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theOwner, tvwChild, , strCustomer)
'Level 5 — Customer/Account
strSQL5 = "SELECT DISTINCT
[Customer] FROM tblMain WHERE [SalesCenter]='" & strCustomer & "'"
Set rsCustomer =
CurrentDb.OpenRecordset(strSQL5)
With rsCustomer
.MoveFirst
Do Until .EOF
strCustomer =
.Fields("Customer")
Set theCustomer =
Me.tvwNodes.Nodes.Add(theCustomer, tvwChild, , strCustomer)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
rsFSMG.Close
rsRegion.Close
rsOwner.Close
rsCustomer.Close
rsCustomer.Close
Set rsFSMG = Nothing
Set rsRegion = Nothing
Set rsOwner = Nothing
Set rsCustomer = Nothing
Set rsCustomer = Nothing
Me.visible = True
DoCmd.Close acForm, "frmLoadingFormMessage"



PROC_EXIT:
Exit Sub
PROC_ERROR:
Call ShowError("frmViewPromotions", "Form_Load", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

treeview control issue 1

Top