Display Database File using TreeView, ListView and ADO

• Download source- 20.92 KB

Introduction

I have a question in my email, "How to use ADO to populate TreeView and ListView with data from *.mdb file?"
I try to present the answer in the following lines, I hope I succeed.
This article show how to:

• Connect with database
• Populate TreeView with Tables name and Fields name
• Populate ListView with Records of selected Table

Background

The demonstration Project has one Form. I add the following controls to my Form (frmDataView):

• Two Labels (lblDatabase) to display the file name and (lblTableName) to display the table name
• Two Buttons, one (cmdLoadData) to connect with database file, other (cmdClose) to end show
• ImageList (ImageList1) to load some icons
• TreeView (tvData) and ListView (lvData).

Using the Code 

' Here is the code to get Tables name from the database file:
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim MyDataFile As String
Dim strCon As String
Dim tabName As String
Dim dbNode As Node
Dim tabNode As Node

   ' Don't use: On Error goto label, but use following:
   On Error Resume Next
   ' set root node of TreeView.
   Set dbNode = tvData.Nodes.Add(, , "RootDB", "Database", "DatabaseClose")
   dbNode.Tag = "RootDB"
   MyDataFile = App.Path & "\DataFile\" & "test.mdb"
   strCon = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDataFile & ";"
   Set cn = New ADODB.Connection
   cn.CursorLocation = adUseClient
   cn.Open strCon
   Set rst = cn.OpenSchema(adSchemaTables)
   rst.MoveFirst
   Do Until rst.EOF
      If rst("TABLE_TYPE").Value = "VIEW" Or rst("TABLE_TYPE").Value = "TABLE" Then
         tabName = rst("TABLE_NAME").Value
         Set tabNode = tvData.Nodes.Add("RootDB", tvwChild, "X" & tabName, _
		tabName, "TableClose")
         tabNode.Tag = "Tables"
      End If
      rst.MoveNext
   Loop
   rst.Close

' Here is the code to get Fields name from all Tables:
Dim rs As ADODB.Recordset
Dim fldName As String
Dim n As Integer
Dim c As Integer
Dim i As Integer
Dim j As Integer
   Set rs = New ADODB.Recordset
   c = tvData.Nodes("RootDB").Children
   ' first table
   n = tvData.Nodes("RootDB").Child.FirstSibling.Index
   For i = n To c + n
      tabName = tvData.Nodes(i).Text
      ' get field name
      Set rs = cn.Execute("SELECT * FROM [" & tabName & "]", 1, 1)
      For j = 0 To rs.Fields.Count - 1
         fldName = rs.Fields(j).Name
         Set fldNameNode = tvData.Nodes.Add("X" & tabName, tvwChild, _
		"X" & fldName, fldName, "imgField")
         fldNameNode.Tag = "Fields"
      Next j
      rs.Close
   Next i  

You can read the full code in the Form (frmDataView):

• InitListView Sub: To initialize ListView
• LoadListView Sub: To load ListView with data
• cmdLoadData_Click Sub: To connect with database file
• tvData_Collapse Sub: To change icon
• tvData_Expand Sub: To change icon
• tvData_NodeClick Sub: To see how to populate ListView with Records when clicking Table name in TreeView, you can also see how we change icon in ColumnHeader of ListView when clicking Field name in TreeView

Remarks

When you create a new project, you must add ListView, TreeView and ImageList controls to ToolBox from Components dialog:
Microsoft Windows Common Controls 6.0
You must add the reference: "Microsoft ActiveX Data Objects 2.x".

Last Words

I hope this article is useful and helps you to display Tables, Fields and Records from a database file to TreeView and ListView. Please tell me if you have any ideas or if you find any problems. Thanks to CodeProject and thanks to all.

Mostafa Kaisoun
M_Kaisoun@hotmail.com

History

• 18^th October, 2009: Initial post