cdbexplorer.html

《VB6数据库开发指南》所有的例程的源码

to obtain a DOA object reference and passes that reference on to LVListProperties to 
populate the property list.
<pre>
Public Sub ListProperties(nd As Node)

  Dim obj As Object
  
  Set obj = GetDAOObjectFromNode(nd)
  LVListProperties obj

End Sub
</pre>

<h3>Expaning a Node with ExpandNode</h3>
Like the GetDAOObjectFromNode procedure, this procedure looks more complex but is 
basically a long Select Case statement. It starts by checking the Boolean 
mblnInTVInit flag so that it can immediately exit if it is fired as a side effect of 
expanding the root node in the TVInit procedure. If the mblnInTVInit flag is clear, it
proceeds to clear all child nodes from the node passed as a parameter. Then it determines 
what nodes need to be populated by calling GetDAOObjectFromNode, after which it enters 
the Select Case block, where it populates whatever nodes are the children of the database
object. Note that this procedure <i>does not</i> call the ListProperties method, nor does the 
form call ListProperties in the Expand event of the treeview. The standard behavior of an 
Explorer interface has a degree of independence between the tree and the list. The list 
pane is only populated when a node in the tree is selected. If it is expanded by clicking 
the plus sign next to the node, the list remains unchanged. Only when the actual node itself 
is select is the list pane updated. The NodeClick event of the treeview control is fired 
when that happens, and that event procedure in the form must call ListProperties. This 
procedure must be called from within the Expand event procedure of the treeview.
<pre>
Public Sub ExpandNode(nd As Node)

  Dim obj As Object
  Dim strTypeName As String
  Dim strObjName As String
  
  ' skip this if within the TVInit procedure
  ' it expands the root node
  If mblnInTVInit Then
    Exit Sub
  End If
  
  ' clear all existing child nodes
  Do While nd.Children > 0
    mtvw.Nodes.Remove nd.Child.Index
  Loop
  
  ' get the associated dao object
  Set obj = GetDAOObjectFromNode(nd)
  
  ' expand based on the typename of the object
  strTypeName = TypeName(obj)
  Select Case strTypeName
    Case "Database"
      TVInit
    Case "TableDefs"
      mdb.TableDefs.Refresh
      TVGetTableDefs
    Case "QueryDefs"
      mdb.QueryDefs.Refresh
      TVGetQueryDefs
    Case "Relations"
      mdb.Relations.Refresh
      TVGetRelations
    Case "TableDef"
      strObjName = obj.Name
      ' add the fields and indexes nodes
      ' with blank child nodes so they can be expanded
      mtvw.Nodes.Add _
        strObjName, tvwChild, _
        strObjName & "Fields", "Fields"
      mtvw.Nodes.Add _
        strObjName & "Fields", tvwChild
      ' indexes
      If mdb.TableDefs(strObjName).Indexes.Count > 0 Then
        mtvw.Nodes.Add _
          strObjName, tvwChild, _
          strObjName & "Indexes", "Indexes"
        mtvw.Nodes.Add _
          strObjName & "Indexes", tvwChild
      End If
    Case "QueryDef"
      strObjName = obj.Name
      ' add the fields and parameters nodes
      ' with blank child nodes so they can be expanded
      mtvw.Nodes.Add _
        strObjName, tvwChild, _
        strObjName & "Fields", "Fields"
      mtvw.Nodes.Add _
        strObjName & "Fields", tvwChild
      ' parameters
      If mdb.QueryDefs(strObjName).Parameters.Count > 0 Then
        mtvw.Nodes.Add _
          strObjName, tvwChild, _
          strObjName & "Parameters", "Parameters"
        mtvw.Nodes.Add _
          strObjName & "Parameters", tvwChild
      End If
    Case "Relation"
      ' add the Fields node and a blank child node
      strObjName = obj.Name
      mtvw.Nodes.Add "Relations" & strObjName, tvwChild, _
        "Relations" & strObjName & "Fields", "Fields"
      mtvw.Nodes.Add _
        "Relations" & strObjName & "Fields", tvwChild
    Case "Fields"
      ' first, fields of what?
      strObjName = nd.Parent.Text
      Select Case nd.Parent.Parent.Text
        Case "TableDefs"
          mdb.TableDefs(strObjName).Fields.Refresh
          TVGetTableDefFields strObjName
        Case "QueryDefs"
          mdb.QueryDefs(strObjName).Fields.Refresh
          TVGetQueryDefFields strObjName
        Case "Relations"
          mdb.Relations(strObjName).Fields.Refresh
          TVGetRelationFields strObjName
      End Select
    Case "Indexes"
      strObjName = nd.Parent.Text
      mdb.TableDefs(strObjName).Indexes.Refresh
      TVGetTableDefIndexes strObjName
    Case "Parameters"
      strObjName = nd.Parent.Text
      mdb.QueryDefs(strObjName).Parameters.Refresh
      TVGetQueryDefParameters strObjName
  End Select

End Sub
</pre>

<hr>

<h2>Creating and Deleting Database Objects</h2>
While the ability to inspect a database is both interesting and useful, the class takes on 
added utility when methods to create and delete database objects are added. Each of the How-To 
examples in the chapter text examined only one set of add and delete routines, but all of them 
are included here. The methods themselves are simple. Each add method calls upon the dialog 
boxes developed for creating the specific type of object, then refreshes the appropriate collection
when the form returns. The delete methods simply delete the object. It is left to the code in the 
form to first determine that an appropriate object is selected for a delete and enable or disable the 
command interface before calling the delete methods. The add methods can be called at any time. The 
code within the methods determines if any part of the creation dialog can be preset based on the 
currently selected node.

<h3>Creating and Deleting Tables</h3>
The AddTable method can be called at any time (as long as a database is open). It shows the 
CreateTableDef form modally and refreshes the TableDefs collection node when the form returns.
<pre>
Public Sub AddTable()

  Load frmCreateTableDef

  Set frmCreateTableDef.Database = mdb

  frmCreateTableDef.Show vbModal

  ' refresh the tabledefs node
  ExpandNode mtvw.Nodes("TableDefs")

End Sub
</pre>
The DeleteTable method takes the name of a TableDef object as a parameter, deletes the table 
from the TableDefs collection of the database and refreshes the TableDefs collection branch 
of the tree. It is up to the form to determine that a table is selected and obtain its name 
before calling delete. The form can use the read-only NodeType property (shown below) to 
determine if the current node is associated with a TableDef object.
<pre>
Public Sub DeleteTable(strTableDefName As String)

  ' delete the tabledef
  mdb.TableDefs.Delete strTableDefName
  
  ' refresh the tree
  ExpandNode mtvw.Nodes("TableDefs")

End Sub
</pre>

<h3>Creating and Deleting Indexes</h3>
Like the AddTable method, the AddIndex method can be called with any object selected in the database. 
However, this codes takes the extra step of determining if the current object is anywhere 
within the branch of a TableDef object and, if so, passes the name of the TableDef into the 
CreateIndex form (saving one step for the user).
<pre>
Public Sub AddIndex()

  Dim obj As Object

  Set obj = GetDAOObjectFromNode(mtvw.SelectedItem)

  Select Case TypeName(obj)
    Case "TableDef"
      ' intialize the form with a table name
      frmCreateIndex.Initialize mdb, obj.Name
    Case "Indexes"
      frmCreateIndex.Initialize _
        mdb, mtvw.SelectedItem.Parent.Text
    Case "Index"
      frmCreateIndex.Initialize mdb, _
        mtvw.SelectedItem.Parent.Parent.Text
    Case "Field"
      ' if it's a table field, get the table name
      ' the great grand parent node tells the type
      If mtvw.SelectedItem.Parent.Parent.Parent.Text _
        = "TableDefs" Then
        ' get the name from the grand parent node
        frmCreateIndex.Initialize _
          mdb, _
          mtvw.SelectedItem.Parent.Parent.Text
      Else
        frmCreateIndex.Initialize mdb
      End If
    Case Else
      frmCreateIndex.Initialize mdb
  End Select

  frmCreateIndex.Show vbModal

  ' check cancel flag
  If Not frmCreateIndex.Cancelled Then
    ' expand the tabledef node
    ExpandNode _
      mtvw.Nodes(frmCreateIndex.TableDefName)
    ' now expand the index node for the tabledef
    ExpandNode _
      mtvw.Nodes(frmCreateIndex.TableDefName & "Indexes")
  End If

End Sub
</pre>

The DeleteIndex method also relies on the form to determine that an index is selected and to 
pass the name of the index and the name of the table to the method. It then deletes the index
using the Delete method of the TableDef's Indexes collection and refreshes the Indexes branch 
of the tree under the TableDef object node.
<pre>
Public Sub DeleteIndex( _
  strTableDefName As String, _
  strIndexName As String)

  ' delete the index from the indexes collection of the
  ' tabledef provided
  mdb.TableDefs(strTableDefName).Indexes.Delete strIndexName
  ' refresh the tree
  ExpandNode mtvw.Nodes(strTableDefName & "Indexes")
  
End Sub
</pre>

<h3>Adding and Deleting Relations</h3>
Defining relationships between tables is a key to maintaining relational integrity and acceptable 
performance in a database. The AddRelation and DeleteRelation methods allow for the addition and 
removal of Relation objects. These methods work in manner similar to those used to add and remove 
tables and indexes.
<p>
The AddRelation method can be called anytime a database is open. It shows the CreateRelation form 
modally and refreshes the Relations collection when the form returns.
<pre>
Public Sub AddRelation()
' load the form to create a relation

  Load frmCreateRelation
  
  ' pass it the database reference
  Set frmCreateRelation.Database = mdb
  
  frmCreateRelation.Show vbModal

  ' refresh the tabledefs node
  ExpandNode mtvw.Nodes("Relations")

End Sub
</pre>
The DeleteRelation method requires the form to provide the name of a Relation object to delete. It 
calls the Delete method of the Relations collection to remove the Relation, then refreshes the 
Relations brach of the tree.
<pre>
Public Sub DeleteRelation(strRelationName As String)
' delete a relation

  ' delete it
  mdb.Relations.Delete strRelationName
  ' refresh the relations node
  ExpandNode mtvw.Nodes("Relations")
  
End Sub
</pre>

<hr>

<h2>Public Properties</h2>

The CDBExplorer class has only two public properties. The Database property allows for direct
access to the DAO Database object should the Explorer form have a need for it. This is provided 
merely for the sake of completeness.
<pre>
Public Property Get Database() As DAO.Database

  Set Database = mdb

End Property
</pre>
The NodeType property returns the typename of the object associated with the currently 
selected node in the tree. This can be used by the form to enable or disable controls 
based on the current object type. In the sample applications, the top level menu control 
Click events use the NodeType property to enable or disable the Delete commands.
<pre>
Public Property Get NodeType(nd As Node) As String

  Dim obj As Object
  
  Set obj = GetDAOObjectFromNode(nd)
  
  NodeType = TypeName(obj)

End Property
</pre>

<hr>

<h2>Limitations</h2>
<i>
Note: Some of these limitations apply to the entire set of sample applications in chapter 4 and 
not only to the CDBExplorer class.
</i>
<p>
The CDBExplorer class and the object creation forms in the samples are far from totally complete. 
Several important capabilities were not implemented in order to keep the examples relatively 
simple and because of limited space in the book. However, the existing code in the class and 
the forms could be easily extended to provide this functionality.
<p>
The following capabilities were not implemented:
<ul>
	<li>
		Editing of existing objects
		<br>
		Although you can create and delete tables, fields, indexes, and relations, you cannot 
		edit them once they have been created.
	</li>
	<li>
		Missing field types
		<br>
		Not all of the field types supported by Jet were implemented in the CreateTableDef form.
	</li>
	<li>
		Object lists
		<br>
		A more complete implementation of the list pane in an Explorer view would supply lists of 
		associated objects when a collection is selected and allow navigation by using the list 
		as well as the tree. This functionality could be added by expanding on the existing code
		in the class.
	</li>
	<li>
		Fields collection of Index objects
		<br>
		In order to reduce the complexity of the class and eliminate another level of depth in 
		the tree, this collection was not implemented. It could be easily added by extending the 
		GetDAOObjectFromNode function and related procedures.
	</li>
	<li>
		Property lists
		<br>
		The property lists simply pass on the values of the Property objects. It would be helpful 
		if some of the properties were mapped to their more commonly used names. For example, the 
		Type property of a Field object is stored as a number, but it most developers are more
		familiar with names such as "Long Integer", "Text", "Memo" and so on. The CreateTableDef 
		form maps these names into the field type combo box, but the property list does not.
	</li>
</ul>
Most of these capabilities were not implemented due to scheduling constraints or limited space 
in the book. Given a few days work on the existing project, a complete and robust database 
management tool could be developed.
<hr>

</body>
</html>