📄 cdbexplorer.html
字号:
<html>
<head>
<title>The CDBExplorer Class</title>
<body bgcolor="#FFFFFF">
<h1>The CDBExplorer Class</h1>
Due to the amount of code in this class, it was not possible to include all of it in the
text of the book. This file provides additional details on the inner workings of the
CDBExplorer class.
<p>
The class works by coordinating three object variables declared in the Declarations section
of the class module. A Database object, representing the database being inspected, a TreeView
control object representing the tree pane of an Explorer style split window, and a ListView control
object representing the list pane of the Explorer view.
<p>
An Explorer style form that uses the class calls the ExploreDatabase method, passing the database name,
a reference to the treeview, and a reference to the listview. The form should also pass its Expand and
NodeClick events for the treeview on to the class, calling the ExpandNode and ListProperties methods of
the class. These three methods are all that's required for a form to fully implement a read-only
inspection of a database, presented as a hierarchy of objects in the tree, with the object properties
presented in the list.
<p>
Additional methods were added to the class to provide the ability to create and delete tables, indexes,
and relationships.
<hr>
<h2>The Declarations Section</h2>
The following is the declarations section of the class.
<pre>
Option Explicit
' database object
Dim mdb As Database
' Treeview control objects
Private mtvw As TreeView
' Listview control objects
Private mlvw As ListView
' flag for initialization routine
Private mblnInTVInit As Boolean
Private Enum dbExpNodeType
ntDatabase = 0
ntRootCollection = 1
ntRootObject = 2
ntObjectCollection = 3
ntObject = 4
End Enum
</pre>
The mdb, mtvw, and mlvw object variables represent the database and the tree and list
controls on the Explorer form. The mblnInTVInit variable is a Boolean flag used to indicate
that the tree is being initialized so that the ExpandNode method can ignore Expand events
passed on by the form.
<p>
The dbExpNodeType enumeration provides symbolic constants that represent the
"depth" in the tree of a node. The tree closes follows the DAO model of object
containment in a database:
<ul>
<li>
Database Object
<ul>
<li>
TableDefs Collection
<ul>
<li>
TableDef Object
<br>
<i>One node per TableDef in the database.</i>
<ul>
<li>
Fields Collection
<ul>
<li>
Field Object
<br>
<i>One node per Field in the TableDef.</i>
</li>
</ul>
</li>
<li>
Indexes Collection
<ul>
<li>
Index Object
<br>
<i>One node per Index in the TableDef.</i>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>
QueryDefs Collection
<ul>
<li>
QueryDef Object
<br>
<i>One node per QueryDef in the database.</i>
<ul>
<li>
Fields Collection
<ul>
<li>
Field Object
<br>
<i>One node per Field in the QueryDef.</i>
</li>
</ul>
</li>
<li>
Parameters Collection
<ul>
<li>
Parameter Object
<br>
<i>One node per Parameter in the QueryDef.</i>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>
Relations Collection
<ul>
<li>
Relation Object
<br>
<i>One node per Relation in the database.</i>
<ul>
<li>
Fields Collection
<ul>
<li>
Field Object
<br>
<i>One node per Field in the Relation.</i>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ul>
The Fields collection of the Index objects was omitted, although it could easily be added by extending the
same code used to control the other nodes at the same level in the tree.
<hr>
<h2>Event Procedures</h2>
There is no Class_Initialize procedure because all of the initialization code is contained within
the ExploreDatabase method. The Class_Terminate event procedure is a simple cleanup routine that
releases the module level object variables.
<pre>
Private Sub Class_Terminate()
' clean up
On Error Resume Next
mdb.Close
Set mdb = Nothing
Set mtvw = Nothing
Set mlvw = Nothing
End Sub
</pre>
<hr>
<h2>Private Procedures</h2>
The bulk of the work in the class is done in several private procedures. These procedures are used
to initialize the tree and list and populate the controls with database objects.
<h3>TVInit</h3>
This procedure initializes the TreeView after a database has been opened. The root node
for the database is added, followed by three nodes for the TableDefs collection, the
QueryDefs collection, and the Relations collection. A blank dummy node is added under
each collection node so that the collection node will be expandable for the user.
Finally, the root node is expaned to reveal the three top level collection nodes. A
Boolean flag is set so that the ExpandNode method (called from the Expand event procedure
on the form) can ignore the event that is generated by expanding the node.
<pre>
Private Sub TVInit()
' initialize the treeview
Dim strDBName As String
Dim nd As Node
strDBName = mdb.Name
' add db and first level nodes
With mtvw.Nodes
.Clear
Set nd = .Add(, , strDBName, strDBName)
' Add the root nodes under the database
' a bogus node is added under each to make it expandable
Set nd = .Add _
(strDBName, tvwChild, "TableDefs", "TableDefs")
Set nd = .Add("TableDefs", tvwChild)
Set nd = .Add _
(strDBName, tvwChild, "QueryDefs", "QueryDefs")
Set nd = .Add("QueryDefs", tvwChild)
Set nd = .Add _
(strDBName, tvwChild, "Relations", "Relations")
Set nd = .Add("Relations", tvwChild)
mblnInTVInit = True
.Item(strDBName).Expanded = True
mblnInTVInit = False
End With
End Sub
</pre>
<h3>TVGetTableDefs</h3>
This procedure populates the nodes for the TableDefs collection and is called when the
TableDefs node is expanded. The procedure iterates the TableDefs collection of the database,
adding a node for each as well as a blank dummy node so that the TableDef node can be
expanded.
<pre>
Private Sub TVGetTableDefs()
Dim strTDName As String
Dim nd As Node
Dim td As TableDef
With mtvw.Nodes
' refresh the collection
mdb.TableDefs.Refresh
For Each td In mdb.TableDefs
' skip system tables
If (td.Attributes And dbSystemObject) = 0 Then
strTDName = td.Name
' add the node for the table
Set nd = .Add _
("TableDefs", tvwChild, strTDName, strTDName)
' add a bogus node so it can be expanded
Set nd = .Add(strTDName, tvwChild)
End If
Next ' TableDef
End With
End Sub
</pre>
<h3>TVGetQueryDefs</h3>
This procedure populates the QueryDefs collection and is called when the user expands the
QueryDefs collection node. Like the TVGetTableDefs procedure, the code iterates the QueryDefs
collection, adding a node for the QueryDef and a dummy child node for each QueryDef object in
the database.
<pre>
Private Sub TVGetQueryDefs()
Dim strQDName As String
Dim nd As Node
Dim qd As QueryDef
With mtvw.Nodes
' refresh the collection
mdb.QueryDefs.Refresh
For Each qd In mdb.QueryDefs
strQDName = qd.Name
' add the node for the query
.Add "QueryDefs", tvwChild, strQDName, strQDName
' add nodes for fields and parameters
' add a blank node so it's expandable
.Add strQDName, tvwChild
Next ' QueryDef
End With
End Sub
</pre>
<h3>TVGetRelations</h3>
This procedure populates the Relations collection under the main Relations node in the tree
using the same technique used for the TableDefs and QueryDefs collection.
<pre>
Private Sub TVGetRelations()
Dim strRelName As String
Dim rel As Relation
With mtvw.Nodes
' refresh collection
mdb.Relations.Refresh
For Each rel In mdb.Relations
strRelName = rel.Name
.Add "Relations", tvwChild, _
"Relations" & strRelName, strRelName
' add a blank child node so that it can be expanded
.Add "Relations" & strRelName, tvwChild
Next ' Relation
End With
End Sub
</pre>
<h3>Populating TableDef Child Objects</h3>
The TVGetTableDefFields and TVGetTableDefIndexes procedures populate the Fields and Indexes
collection nodes in the tree. The same technique used for populating other collections is used.
A node is added to the tree for each object in the collection.
<pre>
Private Sub TVGetTableDefFields(strTableDefName As String)
Dim fld As Field
Dim strFieldName As String
With mtvw.Nodes
' refresh collection
mdb.TableDefs(strTableDefName).Fields.Refresh
For Each fld In mdb.TableDefs(strTableDefName).Fields
strFieldName = fld.Name
.Add strTableDefName & "Fields", tvwChild, _
strTableDefName & "Fields" & strFieldName, _
strFieldName
Next ' Field
End With
End Sub
Private Sub TVGetTableDefIndexes(strTableDefName As String)
Dim idx As Index
Dim strIndexName As String
With mtvw.Nodes
' refresh collection
mdb.TableDefs(strTableDefName).Indexes.Refresh
For Each idx In mdb.TableDefs(strTableDefName).Indexes
strIndexName = idx.Name
.Add strTableDefName & "Indexes", tvwChild, _
strTableDefName & "Indexes" & strIndexName, _
strIndexName
Next ' Index
End With
End Sub
</pre>
<h3>Populating QueryDef Child Objects</h3>
The TVGetQueryDefFields and TVGetQueryParameters procedures populate nodes for
the Fields and Parameters collections of a QueryDef. These procedures are called
when the parent Fields or Parameters nodes are expanded by the user.
<pre>
Private Sub TVGetQueryDefFields(strQueryDefName As String)
Dim fld As Field
Dim strFieldName As String
With mtvw.Nodes
' refresh collection
mdb.QueryDefs(strQueryDefName).Fields.Refresh
For Each fld In mdb.QueryDefs(strQueryDefName).Fields
strFieldName = fld.Name
.Add strQueryDefName & "Fields", tvwChild, _
strQueryDefName & "Fields" & strFieldName, _
strFieldName
Next ' Field
End With
End Sub
Private Sub TVGetQueryDefParameters _
(strQueryDefName As String)
Dim param As Parameter
Dim strParameterName As String
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -