Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse ADO to list database tables and their records in a TreeView
KeywordsADO, database, tables, records, TreeView
CategoriesDatabase
 
When the form loads, connect to the database and call ListTables to load the TreeView with the table names. ListTables uses the ADO Connection object's OpenSchema method to list the tables.
 
' List the tables in the database.
Private Sub ListTables()
Dim rs As ADODB.Recordset
Dim table_node As Node

    ' Clear the TreeView.
    trvData.Nodes.Clear

    ' Use OpenSchema and get the table names.
    ' The final argument in the parameter array
    ' is "Table" to indicate we want a list of tables.
    Set rs = m_Conn.OpenSchema(adSchemaTables, _
        Array(Empty, Empty, Empty, "Table"))
    Do While Not rs.EOF
        Set table_node = trvData.Nodes.Add(, , , _
            rs!TABLE_NAME)
        table_node.Tag = "Table"
        rs.MoveNext
    Loop

    rs.Close
End Sub
 
When the user clicks on a table, the program calls subroutine LoadRecords to load the table's records.

LoadRecords does nothing if the table's records are already loaded. If the records are not loaded, the subroutine loads them.

 
Private Sub trvData_NodeClick(ByVal Node As _
    MSComctlLib.Node)
    ' See what kind of node this is.
    Select Case Node.Tag
        Case "Table"
            ' Load this table's records.
            LoadRecords Node
        Case "Record"
            MsgBox "Record" & Node.Text
    End Select
End Sub

' List the records in this table.
Private Sub LoadRecords(ByVal table_node As Node)
Dim rs As ADODB.Recordset
Dim i As Integer
Dim record_text As String
Dim record_node As Node

    ' Do nothing if the table's records are already loaded.
    If table_node.Children > 0 Then Exit Sub

    ' Get the table's records.
    Set rs = m_Conn.Execute( _
        "SELECT * FROM " & table_node.Text, , adCmdText)
    Do Until rs.EOF
        ' Build the record string.
        record_text = ""
        For i = 0 To rs.Fields.Count - 1
            record_text = record_text & ", " & _
                rs.Fields.Item(i)
        Next i
        record_text = Mid$(record_text, 2)

        ' Add the record node.
        Set record_node = trvData.Nodes.Add(table_node, _
            tvwChild, , record_text)
        record_node.Tag = "Record"
        record_node.EnsureVisible

        ' Get the next record.
        rs.MoveNext
    Loop

    rs.Close
End Sub
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated