CodeItBetter Programming Another VB Programming Blog

'Database - How to Use ADO to list database tables and their records in a TreeView
'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