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

[chrisjk]

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 !!.

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

(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).]

[chrisjk]

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????

[Serge]

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

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

[chrisjk]

Thanks Serge, you truly are a VB Guru!

[Serge]

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

Regards,

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

Serge

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

[Serge]

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

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

Serge

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

[chrisjk]

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

[Lyla]

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.

[Serge]

Sure.

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

[chrisjk]

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!

[Serge]

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

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

[chrisjk]

Thanks Serge

Serge for President!!