Click here to Skip to main content
Click here to Skip to main content

Display Database File using TreeView, ListView and ADO

, 18 Oct 2009
Rate this:
Please Sign up or sign in to vote.
How to use ADO to display Tables, Fields and Records in TreeView and ListView

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

  • 18th October, 2009: Initial post 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

No Biography provided

Comments and Discussions

 
QuestionJust what I wad looking for..how about vb.net version? PinmemberWinky558530-May-10 20:44 
AnswerRe: Just what I wad looking for..how about vb.net version? PinmemberMostafa Kaisoun1-Jun-10 1:34 
GeneralRe: Just what I wad looking for..how about vb.net version? PinmemberWinky55851-Jun-10 14:24 
GeneralRe: Just what I wad looking for..how about vb.net version? PinmemberWinky558510-Jun-10 8:50 
GeneralRe: Just what I wad looking for..how about vb.net version? PinmemberMostafa Kaisoun11-Jun-10 17:29 
GeneralDisplay Database File using TreeView, ListView and ADO Pinmemberelilancry16-Feb-10 23:02 
GeneralRe: Display Database File using TreeView, ListView and ADO PinmemberMostafa Kaisoun18-Feb-10 5:57 
GeneralThank you PinmemberMember 42990582-Dec-09 20:58 
GeneralRe: Thank you PinmemberMostafa Kaisoun3-Dec-09 3:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 18 Oct 2009
Article Copyright 2009 by Mostafa Kaisoun
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid