DAO Advanced Programming

by Dan Haught
Vice President of ProdUCt Development
http://www.fmsinc.com

Introduction

Data Access Objects, or DAO, is a powerful programming model for database services. Originally designed as an ODBC layer for Microsoft Visual Basic version 2.0, DAO has evolved into a model that encompasses Microsoft Jet and ODBC, and in the future, OLEDB. This session covers advanced topiCS in DAO programming that will give you the tools you need to become an advanced programmer using the DAO model.

This session is of primary value to developers creating database applications using Microsoft Access, Microsoft Visual Basic, Microsoft Excel, or Microsoft Visual C++.

Some of the material here is based on work done for the "Microsoft Jet Database Engine Programmers Guide" from Microsoft Press (ISBN 1-55615-877-7). This book is the most complete reference for DAO and Jet programming.

The following topics will be discussed:

• A Quick Overview of DAO
• Object Creation and Modification
• Working with Data
• Developing an Object Dictionary
• Semi-Documented Tools and Resources
• Wrapping Up

Back to Main Technical Papers Page

Copyright 1996 FMS, Inc. All rights reserved

A Quick Overview of DAO

Up until DAO 3.1, DAO was defined as:

"The Programmatic Interface to Microsoft Jet".

Jet is the database engine used by Microsoft Access and Microsoft Visual Basic. DAO has historically been synonymous with Jet, in that Jet was the database engine that it mapped to. As of DAO version 3.1, an important change has occurred. DAO 3.1, through the ODBC-Direct extensions, offers for the first time the ability to completely bypass Microsoft Jet. It is because of this important new functionality that at a conceptual level, DAO is no longer tied to Jet. It is now defined as:

"A Programmatic Interface for Database Services"

Where Can I Use DAO?

DAO is available as a programming tool in to following development environments:

• Access 2.0
• Access 95 (version 7)
• Microsoft Visual Basic (version 4)
• Microsoft Excel (version 7)
• Microsoft Visual C++
• Any development environment that can use an OLE Typelib

How Do I Get DAO?

The DAO components are available in:

• Access 2.0
• Access 95 (version 7)
• Microsoft Visual Basic (version 4)
• Microsoft Excel (version 7)
• Microsoft Visual C++ (version 2.01 and later)
• The DAO SDK ships with the book "Microsoft Jet Database Engine Programmers Guide" from Microsoft Press

How Does DAO Licensing Work?

If your application's users have any of the following products installed on their workstations:

• Microsoft Office 95
• Visual C++
• Microsoft Excel version 7
• Microsoft Access version 7
• Microsoft Visual Basic version 4 (32-bit)

then they can use both the DAO part of your application and DAO to write their own applications without restrictions. If your application's users don't have any of these products installed, then they can only use DAO in the context of your application. They cannot use DAO to write their own applications by using the DAO component you ship with your application.

A Brief History of DAO

DAO had its genesis as a part of Visual Basic 2.0 known internally at Microsoft as "VT Objects". This component allowed ODBC access with a very limited set of options. With the November 1992 introduction of Access 1.0, DAO entered version 1.0 status, and allowed Access developers to use a limited set of database objects in a Microsoft Jet database. With version 2.0 of DAO, as introduced by Access 2.0, a much fuller object model was available. There was almost complete control over creating and modifying objects, and opening a variety of recordset types against data. Additionally, the concept of programmatic security control was introduced. The first 32-bit version of DAO was created soon after for the ODBC desktop driver pack introduced by Microsoft. Then, in 1995, DAO 3.0 was introduced with Access 95 and VB4 simultaneously. This version offered a full 32-bit typelib and added new properties and methods to round out the object model. In addition, a new Error object was added to allow easier access to runtime errors. DAO 3.1 was in beta status as of the writing of this paper, and its release date is uncertain. DAO 3.1 represents the first break with Microsoft Jet. Since version 3.1 allows direct access to ODBC data sources through its ODBC-Direct functionality, DAO is no longer only an interface to Jet. In the future, DAO will be the programmatic object model for OLE DB.

The following diagram illustrates the evolution of DAO.

`

Why Use DAO?

DAO is a well-known, easy to use object model for database services, including Microsoft Jet, ODBC, and in the future, OLE DB. DAO is also multi-lingual-you can use it from VB and VBA environments, FoXPro, and Visual C++. Your knowledge of DAO allows you to access a variety of data sources from a variety of development environments. DAO is also Microsoft's strategic object model for Database Access. Therefore, you can assume that it is going to be around for a long while.

The DAO Model

DAO is implemented as a hierarchy of objects and collections. Each of the object types is represented within this hierarchy. The following diagram illustrates the model:

Objects and Collections

Most object types have a collection that acts as a holder for all the objects of that specific type. For example, each the QueryDefs collection holds all QueryDef objects in the database. The following syntax is used to refer to objects in a collection:

Collection!name or Collection![Name]

Collection("name")

Collection(expression) where expression is a variable or other expression

Collection(index) where index is the number of the object's position within the collection.

Traversing collections

You can traverse or iterate through a DAO collection in one of two ways:

For intCounter = 0 To object.collection.Count - 1' do something with the objectNext intCounter

or, if you are using VBA, you can use

For Each object In Collection' do something with the objectNext object

The second form is obviously more compact and readable. (This is just one of the examples of how VBA is superior to Visual Basic or Access Basic for working with collections).

Opening and Closing Object Variables

With VBA (and Visual Basic and Access Basic in earlier versions), you can set object variables to represent an object in the DAO hierarchy. For example, the following code sets a TableDef object variable to a specific table in the database, and then uses that variable to point to the TableDef:Dim tdfTmp As TableDefSet tdfTmp = DBEngine.Workspaces(0).Databases(0).TableDefs("Customers")Debug.Print tdfTmp.Name

To close the object variable, use the Close method. Note that there are certain objects which you do not want to close without understanding how DAO interacts with object close commands:

• Version 3 of DAO allows you to close a Workspace object, but it closes all of that workspace's child objects.
• If you close the current database in Access using the DBEngine(0)(0) syntax, any other DAO variables you have under DBEngine(0)(0) will also be closed.

There are other internal idiosyncracies that are documented in the README.TXT file that ships with Access 95 and VB4.

Object Creation and Modification

DAO allows you to create new objects, and modify existing ones. This section outlines the steps necessary to accomplish this.

Creating Objects

To create an object, the general steps are:

1. Use the appropriate Create method
2. Set properties
3. Create child objects
4. Set child object properties
5. Append it to the collection

The rest of this section shows the SQL and DAO ways to create objects.

Creating a Database

DAO:Dim dbNew As DatabaseSet dbNew = CreateDatabase("C:\MYTEST.MDB, dbLangGeneral, dbVersion30)

SQL:

No SQL equivalent.

DAO:Dim dbsTmp As DatabaseDim tdfTmp As TabledefDim fldTmp As FieldSet dbsTmp = OpenDatabase("C:\MYTEST.MDB")Set tdfTmp = dbsTmp.CreateTableDefs("MyTable")Set fldTmp = tdfTmp.CreateField("MyField", dbText)tdfTmp.Fields.Append fldTmpdbsTmp.TableDefs.Append tdfTmpdbsTmp.Close

SQL:Dim dbsTmp As DatabaseSet dbsTmp = OpenDatabase("C:\MYTEST.MDB")dbsTmp.Execut ("CREATE TABLE MyTable (MyField Text);")dbsTmp.Close

Setting Field Properties

DAO:

Dim dbs As DatabaseDim tdf As TableDefDim fldID As FieldDim fldName As FieldDim fldResponse As FieldDim fldClass As FieldSet dbs = OpenDatabase("C:\MYTEST.MDB")Set tdf = dbs.CreateTableDef("Marketing Survey")set FldID = tdf.CreateField("ID", dbInteger)fldID.Required = TrueSet fldName = tdf.CreateField("Name", dbText)fldName.Required = TruefldName.Size = 40fldName.AllowZeroLength = TruefldName.DefaultValue = "Unknown"Set fldResponse = tdf.CreateField("Response", dbMemo)set fldClass = tdf.CreateField("Class", dbText, 10fldClass.Required = TruefldClass.ValidationRule = "in('A','B','X')"fldClass.ValidationText = "Enter of of A, B, or X"tdf.Fields.AppendFldIDtdf.Fields.Append fldNametdf.Fields.Append fldResponsetdf.Fields.Append fldClassdbs.TableDefs.Append tdfdbs.Close

SQL:

Using SQL Data Definition Language statements, you can only specify the field names and data types. Use programmatic DAO access to specify all properties.

Using Temporary Objects

Unlike other objects in the DAO hierarchy, you can create QueryDef objects and use them without having to append them to the QueryDefs collection. This is a powerful feature that allows you to create queries on the fly, execute them, and not have to worry about deleting them from the database when you are done with them.

Modifying Objects

Some properties can't be changed on existing objects. For example, although you can add new fields to an existing table, you cannot change the data type of an existing field. DAO strives to be as lean and efficient as possible. The act of changing a field's data type actually relies on a number of operations such as restructuring the table, converting all the existing data, and writing the converted data back to the original table. If DAO were to support such operations, it would be a much larger component and require much more memory,

Some applications that use DAO, such as Microsoft Access, allow you to change the data type of an existing field. This is accomplished through functionality supplied by Access, not DAO. If you want to modify an object such as the field data type, you must:

1. Create a new table
2. Clone the source table's structure to the new table
3. Change the data type of the desired field while creating the new table
4. Run an Append query that copies the data from the source table to the new table
5. Delete the source table and rename the new table to the source table's name

Types of Properties

When using DAO to access Jet databases, it is important to understand the types of properties available through Jet. Properties are divided into two categories:

Engine-Defined Properties

These properties are defined and managed by Jet. That means that these properties will always exist for new objects. For example, when you create a new field object, it always has a default set of properties such as Type and AllowZeroLength.

User-Defined Properties

These are properties that are defined by the user of Jet. This user can be your application, or an application such as Microsoft Access. When you use Jet to create an object, User-Defined properties do not exist until you create them. As an example, if you create a field object in Microsoft Access using DAO, certain Access-defined (which in this case also means user-defined since Access is the "user" of Jet) properties will not exist (such as "Description") until you either create the property using DAO, or use the Microsoft Access interface to add a value in the Description field.

Working with Data

There are several advanced topics regarding data access using DAO. This section covers these topics.

Choosing the Right Recordset

Microsoft Jet (through DAO) offers several options for recordset types. The type of recordset you choose should be based on your specific data access needs. The available recordset types are:

Table

This type of recordset refers to either a local table in the current database, or a linked table that resides in another database. When you use a Table type recordset, Jet opens the actual table itself, and any changes you make are made to the table directly. Note that a Table-type recordset can only be based on a single table-it cannot be based on a query that joins or unions two or more tables.

Use this type when you need direct access to indexes. For example, if you want to use the Seek method to locate data based on an index, you must use the Table type recordset, since it is the only one that directly supports indexes.

Dynaset

This is the most flexible type of recordset. A dynaset is a logical representation of data from one or more tables that is accessed internally through a set of ordered pointers and pages of data. You can use a dynaset to retrieve or update data based on a set of joined tables (as with a query). You can also represent heterogeneous joins in a recordset-the tables joined into the dynaset can be based on disparate data sources such as Jet databases, Paradox tables, SQL Server data, etc.

Use this type when you want a picture of data against multiple tables, and/or want to update the data in multiple tables.

Snapshot

The Snapshot type or recordset is a static, read-only picture of data. The data in the Snapshot is a fixed picture of the data as it existed when the Snapshot was created.

Use this type of recordset when you want a fixed picture of data, and don't need to make changes to the data.

Advanced Recordset Options

There are variety of options you can use when working with Dynaset and Snapshot recordsets. The following table shows these options:

Inconsistent updates are those that violate the referential integrity of multiple tables represented in a multi-table dynaset. If you need to bypass referential integrity, use the dbInconsistent option and Jet will allow you to do so.

Microsoft Jet Transactions

Microsoft Jet implements a sophisticated transaction model that allows you define sets of data operations as an atomic unit. You can then tell Jet to commit or rollback the unit as a whole. There are several developer issues in working with transactions that you should be aware of.

Scoping of Transactions

Transactions are scoped at the DAO Workspace level. Because of this, transactions are global to the Workspace object, not to a specific database or recordset. If you change data in more than one database, or more than one recordset, and you issue a RollBack command, the Rollback affects all of the objects opened within the workspace.

Issues With the Temporary Database

Database transaction systems typically operate by buffering the data changes within a transaction to a temporary database. Then, once the transaction is told to commit, the contents of the temporary database are merged back into the original database. Jet uses a similar scheme-it buffers transactions in memory until cache memory is exhausted. It then creates a temporary Jet database and writes transaction changes there. This temporary database is created in the directory pointed to by your TEMP variable. If the disk space available for transactions is exhausted, a trappable runtime error occurs, allow you to handle the condition by either freeing up space, or issuing a RollBack to cancel the transaction.

Developing an Object Dictionary

As an example of the object and property interrogation capabilities of DAO, I have included two add-ins with this paper, one for Access version 2.0 and one for Access version 7.0. These add-ins allow you dump the entire DAO structure of a database to an ASCII file.

See the .WRI files that accompany these add-ins for complete information on how to install and use them.

The Access 2.0 Version

First, let's look at the Access 2.0 version and see how it works. All functionality is found in the following procedures found in the module behind the frmDAOStructureDumper form:

Option Compare Database'Use database order for string comparisonsOption ExplicitDim intFileOut As IntegerDim fErrors As IntegerDim intTabs As IntegerDim lngLines As LongConst FMSVersion = "2.0"Sub cmdCancel_Click ()DoCmd CloseEnd SubSub cmdClose_Click ()DoCmd CloseEnd SubSub cmdNotepad_Click ()Dim x As Variantx = Shell("write.exe " & Me!txtFileName, 1)End SubSub cmdStart_Click ()Dim fOK As IntegerIf Me!txtFileName <> "" Then If Me!txtTabs <> "" ThenintTabs = Me!txtTabs End If DoCmd Hourglass True DoCmd GoToPage 2 fOK = fDumpDAO(CStr(Me!txtFileName), CInt(Me!chkProperties), CInt(Me!chkErrors)) Me!cmdNotePad.enabled = True DoCmd Hourglass False Me!txtLines.Caption = lngLines & " lines were written to file: " & Me!txtFileName DoCmd GoToPage 3End IfEnd SubFunction fDumpDAO (strFile As String, fProps As Integer, fErrors As Integer) As Integer' Comments: Dumps the database structure to a text file' In: strFile- path and name of file' fProperties - true to dump properties' fErrors- true to write errors' Out : True/False- success/failure' Revisions' 04/08/96 djh initial version'Dim dbsCurrent As DatabaseDim tdfTmp As TableDefDim fldTmp As FieldDim qdfTmp As QueryDefDim relTmp As RelationDim idxTmp As IndexDim cntTmp As ContainerDim docTmp As DocumentDim prpTmp As PropertyDim intCounter As IntegerDim intBCounter As IntegerDim intCCounter As IntegerDim intPCounter As Integer' Delete the output fileOn Error Resume NextKill strFileOn Error GoTo fDumpDAO_Err' InitializeintFileOut = FreeFileOpen strFile For Output As intFileOutSet dbsCurrent = CurrentDB()lngLines = 0' Write the database infoCall WriteOutput("FMS DAO Dumper Version " & FMSVersion, 0)Call WriteOutput("Generated: " & Now, 0)Call WriteOutput("--------------------------------------------------------------------", 0)If fProps Then For intPCounter = 0 To dbsCurrent.Properties.Count - 1Set prpTmp = dbsCurrent.Properties(intPCounter)Call WriteProps(prpTmp, 1) Next intPCounterEnd If' Iterate the Tabledefs collectionFor intCounter = 0 To dbsCurrent.Tabledefs.Count - 1 Set tdfTmp = dbsCurrent.Tabledefs(intCounter) Call WriteOutput("TABLE: " & tdfTmp.Name, 1) If fProps ThenFor intPCounter = 0 To tdfTmp.Properties.Count - 1Set prpTmp = tdfTmp.Properties(intPCounter)Call WriteProps(prpTmp, 2)Next intPCounter End If ' Iterate the fields collection For intBCounter = 0 To tdfTmp.Fields.Count - 1Set fldTmp = tdfTmp.Fields(intBCounter)Call WriteOutput("TABLE FIELD: " & fldTmp.Name, 2)If fProps ThenFor intPCounter = 0 To fldTmp.Properties.Count - 1 Set prpTmp = fldTmp.Properties(intPCounter) Call WriteProps(prpTmp, 3)Next intPCounterEnd If Next intBCounter ' Iterate the Indexes collection For intBCounter = 0 To tdfTmp.Indexes.Count - 1Set idxTmp = tdfTmp.Indexes(intBCounter)Call WriteOutput("INDEX: " & idxTmp.Name, 2)If fProps ThenFor intPCounter = 0 To idxTmp.Properties.Count - 1 Set prpTmp = idxTmp.Properties(intPCounter) Call WriteProps(prpTmp, 3)Next intPCounterEnd If' Iterate the index fields collectionFor intCCounter = 0 To idxTmp.Fields.Count - 1Set fldTmp = idxTmp.Fields(intCCounter)Call WriteOutput("INDEX FIELD: " & fldTmp.Name, 3)If fProps Then For intPCounter = 0 To fldTmp.Properties.Count - 1Set prpTmp = fldTmp.Properties(intPCounter)Call WriteProps(prpTmp, 4) Next intPCounterEnd IfNext intCCounter Next intBCounterNext intCounter' Iterate the Relations collectionFor intCounter = 0 To dbsCurrent.Relations.Count - 1 Set relTmp = dbsCurrent.Relations(intCounter) Call WriteOutput("RELATION: " & relTmp.Name, 1) If fProps ThenFor intPCounter = 0 To relTmp.Properties.Count - 1Set prpTmp = relTmp.Properties(intPCounter)Call WriteProps(prpTmp, 2)Next intPCounter End If ' Iterate the fields collection For intBCounter = 0 To relTmp.Fields.Count - 1Set fldTmp = relTmp.Fields(intBCounter)Call WriteOutput("RELATION FIELD: " & fldTmp.Name, 2)If fProps ThenFor intPCounter = 0 To fldTmp.Properties.Count - 1 Set prpTmp = fldTmp.Properties(intPCounter) Call WriteProps(prpTmp, 2)Next intPCounterEnd If Next intBCounterNext intCounter' Iterate the querydefs collectionFor intCounter = 0 To dbsCurrent.Querydefs.Count - 1 Set qdfTmp = dbsCurrent.Querydefs(intCounter) Call WriteOutput("QUERY:" & qdfTmp.Name, 1) If fProps ThenFor intPCounter = 0 To qdfTmp.Properties.Count - 1Set prpTmp = qdfTmp.Properties(intPCounter)Call WriteProps(prpTmp, 2)Next intPCounter End If ' Iterate the fields collection For intBCounter = 0 To qdfTmp.Fields.Count - 1Set fldTmp = qdfTmp.Fields(intBCounter)Call WriteOutput("QUERY FIELD: " & fldTmp.Name, 2)If fProps ThenFor intPCounter = 0 To fldTmp.Properties.Count - 1 Set prpTmp = fldTmp.Properties(intPCounter) Call WriteProps(prpTmp, 2)Next intPCounterEnd If Next intBCounterNext intCounter' Iterate the Containers collectionFor intCounter = 0 To dbsCurrent.Containers.Count - 1 Set cntTmp = dbsCurrent.Containers(intCounter) Call WriteOutput("CONTAINER: " & cntTmp.Name, 1) If fProps ThenFor intPCounter = 0 To cntTmp.Properties.Count - 1Set prpTmp = cntTmp.Properties(intPCounter)Call WriteProps(prpTmp, 2)Next intPCounter End If ' Iterate the Documents collection For intBCounter = 0 To cntTmp.Documents.Count - 1Set docTmp = dbsCurrent.Containers(intCounter).Documents(intBCounter)Call WriteOutput("DOCUMENT: " & docTmp.Name, 2)If fProps ThenFor intPCounter = 0 To docTmp.Properties.Count - 1 Set prpTmp = docTmp.Properties(intPCounter) Call WriteProps(prpTmp, 3)Next intPCounterEnd If Next intBCounterNext intCounterfDumpDAO = TrueClose intFileOutfDumpDAO_Exit:Exit FunctionfDumpDAO_Err:If fErrors Then WriteOutput "************** Error: " & Error$, 0End IfResume NextEnd FunctionSub Form_Open (Cancel As Integer)Me!lblVersion.Caption = "Version " & FMSVersionMe!txtFileName = "C:\DAO_DUMP.TXT"Me!chkProperties = TrueMe!chkErrors = TrueMe!txtTabs = 4End SubSub WriteOutput (strOut As String, intIndent As Integer)' Comments: Writes the string out to the file' In: strOut - string to write' intIndent - number of indents' Out' Revisions'Dim strTabs As StringstrTabs = Space(intIndent * intTabs)Print #intFileOut, strTabs & strOutlngLines = lngLines + 1End SubSub WriteProps (prpIn As Property, intIndent As Integer)' Comments: Writes the name and value of the supplied property' In: prpIn - property object' intIndent - number of indents (hobo variable)' Out :' Revisions'Dim intSaveErr As IntegerDim strSaveErr As StringDim strName As StringDim varVal As Variant' Disable error handlerOn Error Resume Next' Get the property name and valuestrName = prpIn.NamevarVal = prpIn.ValueintSaveErr = ErrstrSaveErr = Error$' Reset error handlerOn Error GoTo 0If intSaveErr = 0 Then Call WriteOutput(strName & ": " & varVal, intIndent)Else If fErrors ThenCall WriteOutput("************** " & strName & ": Error (" & strSaveErr & ")", intIndent) End IfEnd IfEnd Sub

The Access 95/VBA Way

Now let's look at the VBA implementation of the same code base. You can see that code is much more efficient because we can now use "late binding"-that is we can Dim Foo As Object, and pass that Object around to subroutines. Also, we can use the For Each…Next construct to walk through collections:

Option ExplicitDim intFileOut As IntegerDim fErrors As IntegerDim intTabs As IntegerDim lngLines As LongConst FMSVersion = "7.0"Private Sub cmdCancel_Click()DoCmd.CloseEnd SubPrivate Sub cmdClose_Click()DoCmd.CloseEnd SubPrivate Sub cmdNotepad_Click()Dim x As Variantx = Shell("write.exe " & Me!txtFileName, 1)End SubPrivate Sub cmdStart_Click()Dim fOK As Integer If Me!txtFileName <> "" Then If Me!txtTabs <> "" ThenintTabs = Me!txtTabs End If DoCmd.Hourglass True DoCmd.GoToPage 2 fOK = fDumpDAO(CStr(Me!txtFileName), CInt(Me!chkProperties), CInt(Me!chkErrors)) Me!cmdNotepad.Enabled = True DoCmd.Hourglass False Me!txtLines.Caption = lngLines & " lines were written to file: " & Me!txtFileName DoCmd.GoToPage 3End IfEnd SubPrivate Function fDumpDAO(strFile As String, fProps As Integer, fErrors As Integer) As Integer' Comments: Dumps the database structure to a text file' In: strFile- path and name of file' fProperties - true to dump properties' fErrors- true to write errors' Out : True/False- success/failure' Revisions' 04/08/96 djh initial version'Dim dbsCurrent As DATABASEDim tdfTmp As TableDefDim fldTmp As FieldDim qdfTmp As QueryDefDim relTmp As RelationDim idxTmp As INDEXDim cntTmp As ContainerDim docTmp As DocumentDim prpTmp As PropertyDim intCounter As IntegerDim intBCounter As IntegerDim intCCounter As IntegerDim intPCounter As Integer' Delete the output fileOn Error Resume NextKill strFileOn Error GoTo fDumpDAO_Err' InitializeintFileOut = FreeFileOpen strFile For Output As intFileOutSet dbsCurrent = CurrentDb()lngLines = 0' Write the database infoCall WriteOutput("FMS DAO Dumper Version " & FMSVersion, 0)Call WriteOutput("Generated: " & Now, 0)Call WriteOutput("--------------------------------------------------------------------", 0)If fProps Then Call WriteProps(dbsCurrent, 1)' Iterate the Tabledefs collectionFor Each tdfTmp In dbsCurrent.TableDefs Call WriteOutput("TABLE: " & tdfTmp.Name, 1) If fProps Then Call WriteProps(tdfTmp, 2) ' Iterate the fields collection For Each fldTmp In tdfTmp.FieldsCall WriteOutput("TABLE FIELD: " & fldTmp.Name, 2)If fProps Then Call WriteProps(fldTmp, 3) Next fldTmp ' Iterate the Indexes collection For Each idxTmp In tdfTmp.IndexesCall WriteOutput("INDEX: " & idxTmp.Name, 2)If fProps Then Call WriteProps(idxTmp, 3)' Iterate the index fields collectionFor Each fldTmp In idxTmp.FieldsCall WriteOutput("INDEX FIELD: " & fldTmp.Name, 3)If fProps Then Call WriteProps(fldTmp, 4)Next fldTmp Next idxTmpNext tdfTmp' Iterate the Relations collectionFor Each relTmp In dbsCurrent.Relations Call WriteOutput("RELATION: " & relTmp.Name, 1) If fProps Then Call WriteProps(relTmp, 2) ' Iterate the fields collection For Each fldTmp In relTmp.FieldsCall WriteOutput("RELATION FIELD: " & fldTmp.Name, 2)If fProps Then Call WriteProps(fldTmp, 2) Next fldTmpNext relTmp' Iterate the querydefs collectionFor Each qdfTmp In dbsCurrent.QueryDefs Call WriteOutput("QUERY:" & qdfTmp.Name, 1) If fProps Then Call WriteProps(qdfTmp, 2) ' Iterate the fields collection For Each fldTmp In qdfTmp.FieldsCall WriteOutput("QUERY FIELD: " & fldTmp.Name, 2)If fProps Then Call WriteProps(fldTmp, 2) Next fldTmpNext qdfTmp' Iterate the Containers collectionFor Each cntTmp In dbsCurrent.Containers Call WriteOutput("CONTAINER: " & cntTmp.Name, 1) If fProps Then Call WriteProps(cntTmp, 2) ' Iterate the Documents collection For Each docTmp In cntTmp.DocumentsCall WriteOutput("DOCUMENT: " & docTmp.Name, 2)If fProps Then Call WriteProps(docTmp, 3) Next docTmpNext cntTmpfDumpDAO = TrueClose intFileOutfDumpDAO_Exit:Exit FunctionfDumpDAO_Err:If fErrors Then WriteOutput "************** Error: " & Error$, 0End IfResume NextEnd FunctionPrivate Sub Form_Open(Cancel As Integer)Me!lblVersion.Caption = "Version " & FMSVersionMe!txtFileName = "C:\DAO_DUMP.TXT"Me!chkProperties = TrueMe!chkErrors = TrueMe!txtTabs = 4End SubPrivate Sub WriteOutput(strOut As String, intIndent As Integer)' Comments: Writes the string out to the file' In: strOut - string to write' intIndent - number of indents' Out' Revisions'Dim strTabs As StringstrTabs = Space(intIndent * intTabs)Print #intFileOut, strTabs & strOutlngLines = lngLines + 1End SubPrivate Sub WriteProps(objIn As Object, intIndent As Integer)' Comments: Writes the name and value of the supplied property' In: objIn - object' intIndent - number of indents (hobo variable)' Out :' Revisions'Dim intSaveErr As IntegerDim strSaveErr As StringDim strName As StringDim varVal As VariantDim prpTmp As PropertyFor Each prpTmp In objIn.Properties ' Disable error handler On Error Resume Next ' Get the property name and value strName = prpTmp.Name varVal = prpTmp.Value intSaveErr = Err strSaveErr = Error$ ' Reset error handler On Error GoTo 0 If intSaveErr = 0 ThenCall WriteOutput(strName & ": " & varVal, intIndent) ElseIf fErrors ThenCall WriteOutput("************** " & strName & ": Error (" & strSaveErr & ")", intIndent)End If End IfNext prpTmpEnd Sub

Exercises for the Reader

You are welcome to extend this add-in add more functionality. For example, you may want to add the ability to write the results to a set of tables in the current database, rather than text files. Additionally, you may want to add to ability to get the structure of non-DAO objects such as forms and reports.

Semi-Documented Tools and Resources

There are several Microsoft-Jet specific tools that you can use that are not fully documented in the documentation that ships with Microsoft products. Although these tools are not DAO-specific, they can make it easier to develop and maintain DAO applications.

TypeLib Browsers

Since the DAO 3.0 component includes an OLE Type Library, you can use a TypeLib browser to view all of the objects, collections, methods and properties, including stuff that is not documented. I have used the following two products with great success:

• OLE2View, from Microsoft which ships as part of Visual C++
• VBA Companion, from Apex Software

ISAMStats

Microsoft Jet contains an undocumented function called ISAMStats that shows various internal values. The syntax of the function is:

ISAMStats ((StatNum As Long [, Reset As Boolean]) As Long

Where StatNum is one of the following values:

See the included add-in (JETMETER.EXE) for an example of how to use this function.

ShowPlan

Microsoft Jet implements a cost-based query optimize in its query engine. During the compilation process of the query, Jet determines the most effective way to execute the query. You can view this plan using the ShowPlan registry setting.

To use this setting, use the Registry Editor that comes with your operating system (REGEDIT.EXE for Windows 95 or REGEDT32.EXE for Windows NT) and add the following key to the registry:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\3.0\Engines\Debug

Under this key, add a string data type entry named JETSHOWPLAN in all capital letters. To turn ShowPlan on, set the value of this new entry to "ON". To turn the feature off, set the value to "OFF". When the feature is on, a text file called SHOWPLAN.OUT is created (or appended to if it already exists) in the current directory. This file contains the query plan(s).

MSLDBUSR.DLL

You can use the 32-bit program LDBVIEW.EXE program to view the users currently logged into a database, or you can use the MSLDBUSR.DLL component for programmatic access to this information. These files and associated documentation are included with this paper.