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
Label
s (lblDatabase
) to display the file name and (lblTableName
) to display the table name - Two
Button
s, one (cmdLoadData
) to connect with database file, other (cmdClose
) to end show ImageList
(ImageList1
) to load some iconsTreeView
(tvData
) and ListView
(lvData
).
Using the Code
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
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
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
n = tvData.Nodes("RootDB").Child.FirstSibling.Index
For i = n To c + n
tabName = tvData.Nodes(i).Text
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 datacmdLoadData_Click Sub
: To connect with database filetvData_Collapse Sub
: To change icontvData_Expand Sub
: To change icontvData_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 ImageL<code>
ist 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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.