Create a Generic ActiveX DLL to Describe Database Objects

May 1998

Return to Article Index
Create a Generic ActiveX DLL
to Describe Database Objects
by Jeffrey Hasan
(Client Server Specialists, Inc.)

Introduction
This month’s article touches on my two favorite Visual Basic topics: Database Access and ActiveX Control / DLL Creation. For my faithful readers this 2-for-1 deal should prove to be a real treat. You will recall that databases, as defined by the "Data Access Object Model" (DAO), are made up of collections of objects, including tables (TableDefs), queries (QueryDefs) and relations (ditto, Relations). The more common of these objects and their associated properties provide a prospective database user with a good high-level overview of the database structure. I have assumed that at a minimum, a prospective database user would like to know the following information about a new database:

How many tables are there, and what are their names?
For each table, what are the fields, and what are their types? (e.g., text, numeric...)
What are the relations between tables: what are the primary and foreign key fields?
Is referential integrity enforced, and if so, how is it defined?

The first part of this article presents you with some code that makes use of the DAO model to provide us with this useful database information. And as if this weren’t enough, the second part of this article describes how to compile our new code into an ActiveX DLL. By doing so, we expose the functionality of our "database description class" to any Visual Basic project by means of a simple reference to the DLL function. Read on, code some, and prepare to impress your friends.
Setting up the ActiveX DLL Project
Before typing in any of the code below, start by opening a new project of the type "ActiveX DLL". This opens up a blank class module, which is where we will place our code. (Be aware that you can also use a "Standard EXE" project, and add a class module, if you don’t want to compile the code into an ActiveX DLL). Then do the following:

Change the name of "Class1" to "Summary", and save the class as "Summary.cls"
Change the name of "Project1" to "DBProject", and save the project as "DBProject"
Open the dialog box "DBProject Properties..." under the "Project" menu. Confirm that the "Project Type" is set to "ActiveX DLL" and that the "Project Name" is set to "DBProject". Under "Project Description" enter some descriptive information about the project, such as "Describe Database Objects". Whatever you type here will appear in the Project-References dialog box, when your user goes to reference your DLL. Hit OK.
Finally, go to the Project-References dialog box, and put a check mark against "Microsoft DAO 3.5 Object Library". Hit OK.

Database Objects and Properties
The database objects that we are concerned with describing are workspaces, databases, tables, fields and relations. You should consult Microsoft’s VB Online Help for more information on these objects and how they relate to each other. Briefly, the workspace object is one of the top-level DAO objects (by default, the first workspace is the zero-th member of the DBEngine collection). Each workspace object has a collection of database objects. The database object properties include the name and path of the database file as well as several more obscure descriptions of the high-level database structure. We won’t get into these here.
Tables are described by the "TableDef" object, whose "Name" property holds the table name. Each TableDef object has a collection of "Field" objects, which have several properties, including "Name" and "Type". The "Type" property is described by a Visual Basic constant which indicates whether the field object holds text, integers, bytes or one of several other data types. Finally, the "Relations" collection of objects describes all the primary/foreign key relationships between tables.
Describing the Database in Code
Our new class, "Summary.cls" accepts a single input from the user: the name and path of the database whose structure they are interested in. For example:
	C:\MSOffice\Access\Office\Samples\Northwind.mdb
The Northwinds database will be our test database for this project. Note that you should adjust the name and path of the database to fit your directory structure. If you do not have Access installed, you will find "Northwind.mdb" or "Nwind.mdb" with your other Visual Basic files. In response to this input, the class will provide a series of message boxes that describe the tables, fields and relations present in the database.
Although our class module expects to be fed a string such as the one above, it does not matter to the class how this string is generated. The user may have the option to graphically select the database or else they may be forced to type in the path word for word. That responsibility is left up to programmer who will use our DLL. Our job is to provide them with some functionality once they have identified the database.
Let’s start by coding a public write-only property for the class so that it can accept the above-mentioned string:
‘Module-level variables
Private DB_Path As String
Private DB_Name As String
Private Count As Integer


Public Property Let DBPath(NewValue As String)
    DB_Path = NewValue
    
    'Derive database name
    For Count = Len(DB_Path) To 1 Step -1

    If Mid(DB_Path, Count, 1) = "\" Then
       DB_Name = Mid(DB_Path, Count + 1, Len(DB_Path))
            
       'Validate existence of database
       If Dir(DB_Path) <> DB_Name Then _
MsgBox "The database '" & DB_Path & "' was" _ " not found!" & vbCrLf & _
"Please check the database path and name.", _ vbExclamation, DB_Name & " not found"
            
       Exit Property
    End If

    Next Count

End Property
The property procedure, DBPath, accepts a string from the user. Next, the string is broken apart to separate the database name from the path. The existence of the database is validated using the Dir function, which returns the database name if the database is found, or else returns "" if it is not. If the database name is not found, the user is alerted. This is good programming practice: although it is not our responsibility that the input be correct, we still have to trap for errors and notify the user that one has occurred.
Note that there is no corresponding "Property Get" procedure to our "Property Let" procedure above. There is of course no need to provide the user with feedback of information that they have provided to the class module themselves.
Finally, having validated the existence of the database, user is ready to call the public method "Describe", which handle the core functionality of the class.
Here is the full code for the "Describe" procedure. Study it, then read below for a discussion of some of the finer points:
Public Sub Describe()

Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim fld As Fields
Dim rl As Relation

'Dim Count As Integer
Dim tblNum As Integer
Dim tblTotal As Integer

Dim strMsg1 As String 'Track system tables
Dim strMsg2 As String 'Track non-system tables

Set ws = DBEngine.Workspaces(0) 'This is a default setting
Set db = ws.OpenDatabase(DB_Path) 'Open the database

'Enumerate all tables
tblTotal = 0
For Each td In db.TableDefs
    If Mid(td.Name, 1, 4) <> "MSys" Then
        strMsg2 = strMsg2 & UCase(td.Name) & vbCrLf
        tblTotal = tblTotal + 1
    End If
Next td
    If strMsg2 <> "" Then MsgBox strMsg2, vbInformation, DB_Name & " Non-System Tables"

'Enumerate all fields
tblNum = 0
For Each td In db.TableDefs
    strMsg2 = ""
    If Mid(td.Name, 1, 4) <> "MSys" Then
        tblNum = tblNum + 1
        strMsg2 = strMsg2 & UCase(td.Name) & _
        "   (" & tblNum & " of " & tblTotal & ")" & vbCrLf

10          For Count = 0 To td.Fields.Count - 1
 strMsg2 = strMsg2 & td.Fields(Count).Name &_ "   {" & FieldType(td.Fields(Count)) & "}" & _ vbCrLf
            Next Count
    Else
        strMsg1 = strMsg1 & td.Name & vbCrLf
    End If
    
If strMsg2 <> "" Then MsgBox strMsg2, vbInformation,   DB_Name & " Non-System Tables"
Next td

'Enumerate the table relations
Count = 0
For Each rl In db.Relations
    strMsg2 = ""
    Count = Count + 1
  strMsg2 = "RELATION OBJECT  (" & Count & " of " & _
db.Relations.Count & ")" & vbCrLf
  strMsg2 = strMsg2 & "PRIMARY: " & vbTab & rl.Table
& "   {" & rl.Name & "}" & vbCrLf
  strMsg2 = strMsg2 & "FOREIGN: " & vbTab &
  	rl.ForeignTable & "   {" & rl.Name & "}" & vbCrLf
20	  strMsg2 = strMsg2 & "RELATION: " & vbTab &
   		RelationType(rl)
    MsgBox strMsg2, vbInformation, DB_Name & " Relations"
Next rl

Set rl = Nothing
Set fld = Nothing
Set td = Nothing
Set db = Nothing
Set ws = Nothing

End Sub
Although I’d like you to think the above code is complicated, it’s really not. All I am doing is opening the database, cycling through its TableDef, Fields, and Relations collections, and reporting the results back to the user in nicely formatted message boxes. Note that I am excluding "Msys" systems tables from consideration. These tables are automatically created by the Jet engine and should not be touched by any but the most experienced user.
Now take a look at line 10. Here, I am reporting the Type properties of the Field objects, which should be written as "fld.Type". Instead, you can see that I am referencing a function called "FieldType()" that will translate the ambiguous numeric code of the type property into a plain-English translation. The code for this private function is:
Private Function FieldType(fld_name As Field) As String

'Translate Field Type code into a string

Select Case fld_name.Type
    Case Is = dbDate
        FieldType = "Date"
    Case Is = dbText
        FieldType = "Text"
    Case Is = dbInteger
        FieldType = "Integer"
    Case Is = dbCurrency
        FieldType = "Currency"
    Case Is = dbSingle
        FieldType = "Single"
    Case Is = dbDouble
        FieldType = "Double"
    Case Is = dbLong
        FieldType = "Long"
    Case Is = dbByte
        FieldType = "Byte"
    Case Is = dbBoolean
        FieldType = "Boolean"
    Case Is = dbMemo
        FieldType = "Memo"
    Case Is = dbLongBinary
        FieldType = "Long Binary"
    Case Else
        FieldType = fld_name.Type
End Select

End Function
Now take a look at line 20. Here, I am reporting the Attributes properties of the Relations objects, which should be written as "rl.Attributes". Instead, you can see that I am referencing a function called "RelationType()" that will translate the ambiguous numeric code of the type property into a plain-English translation. The code for this private function is:
Private Function RelationType(rl_name As Relation) As String

'Translate Relation Type code into a string

Select Case rl_name.Attributes
    Case Is = dbRelationUnique
        RelationType = "One-to-One"
    Case Is = dbRelationDontEnforce
        RelationType = "No Referential Integrity"
    Case Is = dbRelationInherited
        RelationType = "Inherited from Linked Database"
    Case Is = dbRelationUpdateCascade
        RelationType = "Updates will cascade"
    Case Is = dbRelationDeleteCascade
        RelationType = "Deletions will cascade"
    Case Is = 0
        RelationType = "Auto Number"
    Case Else
        RelationType = rl_name.Attributes
End Select

End Function
Once we compile our class module as a DLL, and reference it to a project, here is a sampling of what you will see as output, for "Northwind.mdb". We start with a listing of the tables:

Next, for each table we list the fields and their types (translated from a numeric code to an English word by our FieldType() function):

Finally, for each relation object we list the joined tables and the nature of their relationship (translated from a numeric code to an English word by our RelationType() function):

Compiling the Project as an ActiveX DLL
This last step is simple, since we already defined our project properties at the outset. Go to the File menu, and select the option: "Make DBProject.dll". This name, you will recall, is what we called out project. You will be prompted for a saving location. I suggest using the directory where your project resides. The process takes a few seconds for Visual Basic to compile the project, write the DLL, and reference it in the registry.
Testing our DLL is easy in Visual Basic 5, since we can add a standard EXE to our project to create a project group. First though, we need to reference our new DLL function. Go to "Project-References" and locate the entry: Describe Database Objects. Place a checkmark against it, and hit OK. Take a look now at the Object Browser, under the View menu. Under the drop down box locate the "DBProject" library, and voila! There you see our "Summary" class. Highlight its icon and you will see 2 entries: the public property "DBPath" and the public method "Describe". For usage on these members, highlight their icons. Don’t you feel proud of yourself right now?
Next, go under "File - Add Project", and select a new Standard EXE. Double-click the new form, and place the following code in the Form_Load event:
Dim objSummary As New DBProject.Summary

objSummary.DBPath = "C:\MSOffice\Access\Office\Samples\Northwind.mdb"

objSummary.Describe
Note that the syntax "DBProject.Summary" is optional. You can simply write "As New Summary", unless, that is, your project references more than one "Summary" class. In this case, you should use the "DBProject" project qualifier to distinguish which class you are referencing at the given moment.
Conclusions
By now you should have a better appreciation for:

How to navigate the DAO Object Model
How to write a function that translates Visual Basic numeric constants into English
How to structure a class module, and code its properties and methods, and
How to compile your class into an ActiveX DLL
Once you have thoroughly understood all the code here, you will realize that there are alternative approaches to my choices. Your decisions on how to code the properties, methods and events of your ActiveX DLL function is unimportant as long as the user understands how to access the powerful functionality that you are providing them with.

About the Author

Jeffrey Hasan
Consultant
Client Server Specialists, Inc.
jhasan@cssi.org