If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Quite a few errors in this code, but the worst one (IMO) is getting mixed up with variable names, sometimes you use a variable called rst for the Revordset variable, sometimes rstADO. As you only declared rst (which I have changed), this is a problem. It is the worst because it can be avoided. Put Option Explicit at the head of your code, and you cannot compile with undeclared variabkles, so the compiler will bring your attention to it.

Anyway, this works for me

'>>>>> Always use this next line
Option Explicit
Sub Populate_Treeview()
Dim cnt As ADODB.Connection
'>>>>> be consistent with the variable
Dim rstADO As New ADODB.Recordset
Dim strDb As String, strSQL As String
Dim xlCalc As XlCalculation
Dim j As Long, x As Node '>>>> Not Long!!!!!!!!
Dim lcols
Dim vaData
'SET YOUR VARIABLES HERE:'Path to the database and SQL-statement to execute
strDb = "C:\Gibbos.mdb"
strSQL = "SELECT tbl_TEST.* From tbl_Test"
'In order to increase performance
With Application
xlCalc = .Calculation
.EnableEvents = False
.ScreenUpdating = False
End With
'Establish and ADO connection
Set cnt = New ADODB.Connection
'Create the connection string
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDb & ";"
'Retrieve the recordset and count fields & records
rstADO.Open strSQL, cnt
With rstADO
'>>>>> You canot close the connection and then work with it
lcols = .Fields.Count
'Populate a variant array with the recordset.
vaData = .GetRows
'.ActiveConnection.Close'Set .ActiveConnection = Nothing'Disconnect recordset.
End With
'Add the items to the treeview
With UserForm2 'UserForm1'>>>>> Specify the linestyle
.TreeView1.LineStyle = tvwRootLines
With .TreeView1.Nodes
Set x = .Add(, , rstADO.Fields(2).Name, rstADO.Fields(2).Name)
For j = LBound(vaData, 2) To UBound(vaData, 2)
Set x = .Add(rstADO.Fields(2).Name, tvwChild, , vaData(2, j))
Next j
End With
End With
'Close the recordset, connection and release objects from memory
With rstADO
.Close
.ActiveConnection = Nothing
End With
cnt.Close
Set rstADO = Nothing
Set cnt = Nothing
'Restore the settings
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub