Displaying Tables and their fields from a DB in a Treeview control

Basically what the subject says! I would like to have the tables in an Access 97 DB displayed in a treeview, and when you clicked the little plus sign, it showed all the fields within that table. I have attempted to do it myself, but the treeview control seriously confuses me :confused:!!.

If anyone could help me out here, I would be :eek: and :D

(Aren't these icons great. Okay, I won't over use them anymore - i promise!)

Thanks a million!

Regards,

------------------
- Chris
[email protected]
If it ain't broke - don't fix it :)


[This message has been edited by chrisjk (edited 01-30-2000).]

if I said you were very attractive - would you help me?

Half quote from Monty Python's Flying Circus incase you were worried.

Anyone know how to do this????

Sure. This example is using ADO and ADOX libraries, so make refereces accordingly.
Then try this:

Code:

---

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim xTable As ADOX.Table
    Dim xField As ADOX.Column
    Dim nodTable As Node
    Dim nodField As Node
   
    cn.Provider = "Microsoft.Jet.OLEDB.3.51"
    cn.Open "D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb", "admin", ""
    Set cat.ActiveConnection = cn
    With TreeView1
        For Each xTable In cat.Tables
            If xTable.Type = "TABLE" Then
                If Left(UCase(xTable.Name), 4) <> "MSYS" Then
                    Set nodTable = .Nodes.Add(, , xTable.Name, xTable.Name)
                    For Each xField In xTable.Columns
                        Set nodField = .Nodes.Add(nodTable, tvwChild, , xField.Name)
                    Next
                End If
            End If
        Next
    End With
   
    Set cat = Nothing
    cn.Close
    Set cn = Nothing
End Sub

---

Regards,

------------------

Serge

Programmer Analyst
[email protected]
[email protected]
ICQ#: 51055819

Thanks Serge, you truly are a VB Guru!

Sure. Look for:
Microsoft ADO Ext.2.1 for DDl and Security

Regards,

------------------

Serge

Programmer Analyst
[email protected]
[email protected]
ICQ#: 51055819

Hey Chris, did you just delete the previous post? This is so funny....lol

------------------

Serge

Programmer Analyst
[email protected]
[email protected]
ICQ#: 51055819

I found it just after I posted. I came back to delete post and you had replyed!! Thanks a million, works brilliantly!

Hi Serge.

If you may, How about if we want to show only the records from one table?

In place of tabel name, we use Tabel.keyfield (name/Number) to display the rest of that record's data?

What should we modify in the above to make it work?

Always. Thanx.

Sure.

Code:

---

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim xTable As ADOX.Table
    Dim xField As ADOX.Column
    Dim nodTable As Node
    Dim nodField As Node
   
    cn.Provider = "Microsoft.Jet.OLEDB.3.51"
    cn.Open "D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb", "admin", ""
    Set cat.ActiveConnection = cn
    With TreeView1
        Set xTable = cat.Tables("Customers")
        Set nodTable = .Nodes.Add(, , xTable.Name, xTable.Name)
        For Each xField In xTable.Columns
            Set nodField = .Nodes.Add(nodTable, tvwChild, , xField.Name)
        Next
    End With
   
    Set cat = Nothing
    cn.Close
    Set cn = Nothing
End Sub

---

As you can see I'm using Customers table.

Regards,

------------------

Serge

Programmer Analyst
[email protected]
[email protected]
ICQ#: 51055819

Serge - if you are here, do you think you could possibly show me how to modify it so that it searches an array of strings that hold names of tables I don't want the treeview to display.

Thanks ever so much. You really know your stuff and I hope to be as good as you someday! :)

Sure. Lets assume that I have an arry called arrTables(), which holds names of tables you don't want to see in the TreeView.

Code:

---

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim xTable As ADOX.Table
    Dim xField As ADOX.Column
    Dim nodTable As Node
    Dim nodField As Node
    Dim i As Integer
    Dim bIsFound As Boolean
   
    cn.Provider = "Microsoft.Jet.OLEDB.3.51"
    cn.Open "D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb", "admin", ""
    Set cat.ActiveConnection = cn
    With TreeView1
        For Each xTable In cat.Tables
            If xTable.Type = "TABLE" Then
                If Left(UCase(xTable.Name), 4) <> "MSYS" Then
                    For i = 0 To UBound(arrTables)
                        If UCase(xTable.Name) = UCase(arrTables(i)) Then
                            bIsFound = True
                            Exit For
                        End If
                    Next
                    'Add only if it is not in array
                    If Not bIsFound Then
                        Set nodTable = .Nodes.Add(, , xTable.Name, xTable.Name)
                        For Each xField In xTable.Columns
                            Set nodField = .Nodes.Add(nodTable, tvwChild, , xField.Name)
                        Next
                    Else
                        'Reset the flag
                        bIsFound = False
                    End If
                End If
            End If
        Next
    End With
   
    Set cat = Nothing
    cn.Close
    Set cn = Nothing
End Sub

---

Regards,

------------------

Serge

Programmer Analyst
[email protected]
[email protected]
ICQ#: 51055819

Thanks Serge

Serge for President!! ;)