Presented by Dan Haught
FMS, Inc.
Dan Haught has been developing database applications and tools for more than 10 years on a variety of platforms and has been using Microsoft® Access since its pre–version 1.0 days. Dan manages product development for FMS in Vienna, Virginia, where he develops products for the Total Access line, and is the author of several books on Microsoft Access and the Jet database engine.
E-mail: danh@msn.com
http://www.fmsinc.com
Data Access Objects, or DAO, is a powerful programming model for database services. Originally designed as an ODBC (Open Database Connectivity) layer for Microsoft Visual Basic® programming system 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 the Microsoft Visual C++® development system.
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:
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.”
DAO is available as a programming tool in the following development environments:
The DAO components are available in:
If your application’s users have any of the following products installed on their workstations:
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.
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 Microsoft Access 1.0, DAO entered version 1.0 status and allowed Microsoft Access developers to use a limited set of database objects in a Microsoft Jet database.
With version 2.0 of DAO, as introduced by Microsoft 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 Microsoft Access for Windows 95 and Visual Basic 4.0 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 run-time errors.
DAO 3.5 is included in Office 97 and represents the first break with Microsoft Jet. Since version 3.5 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.
Figure 1. DAO Versions
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 multilingual-you can use it from Visual Basic and Visual Basic for Applications environments, the Microsoft Visual FoxProTM database, 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.
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:
Figure 2. The DAO Hierarchy
Objects and collections
Most object types have a collection that acts as a holder for all the objects of that specific type. For example, 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 object Next intCounter
or, if you are using Visual Basic for Applications, you can use:
For Each object In Collection
' do something with the object
Next object
The second form is obviously more compact and readable. (This is just one of the examples of how Visual Basic for Applications is superior to Visual Basic or Access Basic for working with collections.)
Opening and closing object variables
With Visual Basic for Applications (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 TableDef
Set 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 that 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 Microsoft 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 idiosyncrasies that are documented in the Readme.txt file that ships with Microsoft Access for Windows 95 and Visual Basic 4.0.
DAO version 3.5 provides several powerful new capabilities. The most important are the ODBC-Direct interface and Partial Replication.
DAO 3.5 offers a new technology called ODBCDirect. This feature allows you to deploy client/server applications using Microsoft Access 97 without using Microsoft Jet as the translation layer. ODBCDirect is an interface directly to ODBC, and as such, is faster in many operations.
With ODBCDirect, you have the following advantages (from Microsoft Access 97 Help):
The following diagram shows the changes introduced into the DAO model with ODBCDirect:
Figure 3. The DAO 3.5 Hierarchy Using ODBC-Direct
In DAO/Jet 3.0, replication was pretty much an all or nothing proposition. Version 3.5 of DAO and Jet allows Partial Replication. This means that you can specify which parts of your databases are to be considered for replication.
Version 3.5 of DAO and Jet also implement several lesser changes.
New MaxLocksPerFile setting
There is a new registry setting called MaxLocksPerFile. This allows you to improve performance of the completion of large queries when the data being operated on is on a NetWare or Windows NT®–based server. This forces a transaction to only partially commit.
The SetOption method
You can use the new DBEngine.SetOption method to set various Jet engine parameters at runtime. This allows you to set/test/reset various values without having to restart Microsoft Access and Jet.
Performance improvements
DAO allows you to create new objects and modify existing ones. This section outlines the steps necessary to accomplish this.
To create an object, the general steps are:
The rest of this section shows the SQL and DAO ways to create objects.
Creating a database
DAO:
Dim dbNew As Database
Set dbNew = CreateDatabase("C:\MYTEST.MDB, dbLangGeneral, dbVersion30)
SQL:
No SQL equivalent.
Creating a table
DAO:
Dim dbsTmp As Database
Dim tdfTmp As Tabledef
Dim fldTmp As Field
Set dbsTmp = OpenDatabase("C:\MYTEST.MDB")
Set tdfTmp = dbsTmp.CreateTableDefs("MyTable")
Set fldTmp = tdfTmp.CreateField("MyField", dbText)
tdfTmp.Fields.Append fldTmp
dbsTmp.TableDefs.Append tdfTmp
dbsTmp.Close
SQL:
Dim dbsTmp As Database
Set dbsTmp = OpenDatabase("C:\MYTEST.MDB")
dbsTmp.Execut ("CREATE TABLE MyTable (MyField Text);")
dbsTmp.Close
Setting Field Properties
DAO:
Dim dbs As Database
Dim tdf As TableDef
Dim fldID As Field
Dim fldName As Field
Dim fldResponse As Field
Dim fldClass As Field
Set dbs = OpenDatabase("C:\MYTEST.MDB")
Set tdf = dbs.CreateTableDef("Marketing Survey")
set FldID = tdf.CreateField("ID", dbInteger)
fldID.Required = True
Set fldName = tdf.CreateField("Name", dbText)
fldName.Required = True
fldName.Size = 40
fldName.AllowZeroLength = True
fldName.DefaultValue = "Unknown"
Set fldResponse = tdf.CreateField("Response", dbMemo)
set fldClass = tdf.CreateField("Class", dbText, 10
fldClass.Required = True
fldClass.ValidationRule = "in('A','B','X')"
fldClass.ValidationText = "Enter of of A, B, or X"
tdf.Fields.AppendFldID
tdf.Fields.Append fldName
tdf.Fields.Append fldResponse
tdf.Fields.Append fldClass
dbs.TableDefs.Append tdf
dbs.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.
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.
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 Microsoft Access, not DAO. If you want to modify an object such as the field data type, you must:
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 and user-defined properties.
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 Microsoft Access–defined (which in this case also means user-defined, because Microsoft 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.
There are several advanced topics regarding data access using DAO. This section covers these topics.
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 unites 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, because 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, Microsoft SQL Server™ data, etc.
Use this type when you want a picture of data against multiple tables and/or you 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.
There are variety of options you can use when working with Dynaset and Snapshot recordsets. The following table shows these options.
| Option | Applies To | Description |
| dbAppendOnly | Dynaset | You can specify this option to allow only the addition of new records. Because Jet can dispense with certain internal routines, this option allows a more efficient and faster recordset if all you want is to add data. |
| dbSeeChanges | Table, Dynaset | If you invoke the Edit method on a record and another user changes data in the record before the Update method was invoked, a run-time error will occur. This is helpful if multiple users have simultaneous read/write access to the same data. |
| dbDenyWrite | Dynaset, Snapshot | Prevents other users from modifying data in any of the tables that are included in the recordset. |
| dbDenyRead | Table | Prevents other users from reading data from the table. |
| dbReadOnly | All | Prevents you from making changes in the recordset’s underlying tables (implied on a snapshot). |
| dbForwardOnly | Snapshot | Creates a snapshot that you can only move forward through. This allows Jet to dispense with the creation and maintenance of internal buffers and structures, resulting in a more efficient recordset with faster access. |
| dbSQLPassThrough | Dynaset, Snapshot | Use this option to pass a SQL string directly to an ODBC database for processing. Jet does no internal processing of the query. |
| dbConsistent | Dynaset | Allows only consistent updates.* |
| dbInconsistent | Dynaset | Allows inconsistent updates.* |
*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 implements a sophisticated transaction model that allows you to define sets of data operations as a 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 run-time error occurs, allowing you to handle the condition by either freeing up space or issuing a RollBack to cancel the transaction.
As an example of the object and property interrogation capabilities of DAO, two add-ins are included with this paper, one for Microsoft Access version 2.0 and one for Microsoft Access version 7.0. These add-ins allow you to 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.
First, let’s look at the Microsoft Access 2.0 version and see how it works. All functionality is found in the following procedures, which are found in the module behind the frmDAOStructureDumper form:
Option Compare Database 'Use database order for string comparisons
Option Explicit
Dim intFileOut As Integer
Dim fErrors As Integer
Dim intTabs As Integer
Dim lngLines As Long
Const FMSVersion = "2.0"
Sub cmdCancel_Click ()
DoCmd Close
End Sub
Sub cmdClose_Click ()
DoCmd Close
End Sub
Sub cmdNotepad_Click ()
Dim x As Variant
x = Shell("write.exe " & Me!txtFileName, 1)
End Sub
Sub cmdStart_Click ()
Dim fOK As Integer
If Me!txtFileName <> "" Then
If Me!txtTabs <> "" Then
intTabs = 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 3
End If
End Sub
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 Database
Dim tdfTmp As TableDef
Dim fldTmp As Field
Dim qdfTmp As QueryDef
Dim relTmp As Relation
Dim idxTmp As Index
Dim cntTmp As Container
Dim docTmp As Document
Dim prpTmp As Property
Dim intCounter As Integer
Dim intBCounter As Integer
Dim intCCounter As Integer
Dim intPCounter As Integer
' Delete the output file
On Error Resume Next
Kill strFile
On Error GoTo fDumpDAO_Err
' Initialize
intFileOut = FreeFile
Open strFile For Output As intFileOut
Set dbsCurrent = CurrentDB()
lngLines = 0
' Write the database info
Call 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 - 1
Set prpTmp = dbsCurrent.Properties(intPCounter)
Call WriteProps(prpTmp, 1)
Next intPCounter
End If
' Iterate the Tabledefs collection
For intCounter = 0 To dbsCurrent.Tabledefs.Count - 1
Set tdfTmp = dbsCurrent.Tabledefs(intCounter)
Call WriteOutput("TABLE: " & tdfTmp.Name, 1)
If fProps Then
For intPCounter = 0 To tdfTmp.Properties.Count - 1
Set prpTmp = tdfTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 2)
Next intPCounter
End If
' Iterate the fields collection
For intBCounter = 0 To tdfTmp.Fields.Count - 1
Set fldTmp = tdfTmp.Fields(intBCounter)
Call WriteOutput("TABLE FIELD: " & fldTmp.Name, 2)
If fProps Then
For intPCounter = 0 To fldTmp.Properties.Count - 1
Set prpTmp = fldTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 3)
Next intPCounter
End If
Next intBCounter
' Iterate the Indexes collection
For intBCounter = 0 To tdfTmp.Indexes.Count - 1
Set idxTmp = tdfTmp.Indexes(intBCounter)
Call WriteOutput("INDEX: " & idxTmp.Name, 2)
If fProps Then
For intPCounter = 0 To idxTmp.Properties.Count - 1
Set prpTmp = idxTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 3)
Next intPCounter
End If
' Iterate the index fields collection
For intCCounter = 0 To idxTmp.Fields.Count - 1
Set fldTmp = idxTmp.Fields(intCCounter)
Call WriteOutput("INDEX FIELD: " & fldTmp.Name, 3)
If fProps Then
For intPCounter = 0 To fldTmp.Properties.Count - 1
Set prpTmp = fldTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 4)
Next intPCounter
End If
Next intCCounter
Next intBCounter
Next intCounter
' Iterate the Relations collection
For intCounter = 0 To dbsCurrent.Relations.Count - 1
Set relTmp = dbsCurrent.Relations(intCounter)
Call WriteOutput("RELATION: " & relTmp.Name, 1)
If fProps Then
For intPCounter = 0 To relTmp.Properties.Count - 1
Set prpTmp = relTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 2)
Next intPCounter
End If
' Iterate the fields collection
For intBCounter = 0 To relTmp.Fields.Count - 1
Set fldTmp = relTmp.Fields(intBCounter)
Call WriteOutput("RELATION FIELD: " & fldTmp.Name, 2)
If fProps Then
For intPCounter = 0 To fldTmp.Properties.Count - 1
Set prpTmp = fldTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 2)
Next intPCounter
End If
Next intBCounter
Next intCounter
' Iterate the querydefs collection
For intCounter = 0 To dbsCurrent.Querydefs.Count - 1
Set qdfTmp = dbsCurrent.Querydefs(intCounter)
Call WriteOutput("QUERY: " & qdfTmp.Name, 1)
If fProps Then
For intPCounter = 0 To qdfTmp.Properties.Count - 1
Set prpTmp = qdfTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 2)
Next intPCounter
End If
' Iterate the fields collection
For intBCounter = 0 To qdfTmp.Fields.Count - 1
Set fldTmp = qdfTmp.Fields(intBCounter)
Call WriteOutput("QUERY FIELD: " & fldTmp.Name, 2)
If fProps Then
For intPCounter = 0 To fldTmp.Properties.Count - 1
Set prpTmp = fldTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 2)
Next intPCounter
End If
Next intBCounter
Next intCounter
' Iterate the Containers collection
For intCounter = 0 To dbsCurrent.Containers.Count - 1
Set cntTmp = dbsCurrent.Containers(intCounter)
Call WriteOutput("CONTAINER: " & cntTmp.Name, 1)
If fProps Then
For intPCounter = 0 To cntTmp.Properties.Count - 1
Set prpTmp = cntTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 2)
Next intPCounter
End If
' Iterate the Documents collection
For intBCounter = 0 To cntTmp.Documents.Count - 1
Set docTmp = dbsCurrent.Containers(intCounter).Documents(intBCounter)
Call WriteOutput("DOCUMENT: " & docTmp.Name, 2)
If fProps Then
For intPCounter = 0 To docTmp.Properties.Count - 1
Set prpTmp = docTmp.Properties(intPCounter)
Call WriteProps(prpTmp, 3)
Next intPCounter
End If
Next intBCounter
Next intCounter
fDumpDAO = True
Close intFileOut
fDumpDAO_Exit:
Exit Function
fDumpDAO_Err:
If fErrors Then
WriteOutput "************** Error: " & Error$, 0
End If
Resume Next
End Function
Sub Form_Open (Cancel As Integer)
Me!lblVersion.Caption = "Version " & FMSVersion
Me!txtFileName = "C:\DAO_DUMP.TXT"
Me!chkProperties = True
Me!chkErrors = True
Me!txtTabs = 4
End Sub
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 String
strTabs = Space(intIndent * intTabs)
Print #intFileOut, strTabs & strOut
lngLines = lngLines + 1
End Sub
Sub 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 Integer
Dim strSaveErr As String
Dim strName As String
Dim varVal As Variant
' Disable error handler
On Error Resume Next
' Get the property name and value
strName = prpIn.Name
varVal = prpIn.Value
intSaveErr = Err
strSaveErr = Error$
' Reset error handler
On Error GoTo 0
If intSaveErr = 0 Then
Call WriteOutput(strName & ": " & varVal, intIndent)
Else
If fErrors Then
Call WriteOutput("************** " & strName & ": Error (" & strSaveErr & ")", intIndent)
End If
End If
End Sub
Now let’s look at the Visual Basic for Applications 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 Explicit
Dim intFileOut As Integer
Dim fErrors As Integer
Dim intTabs As Integer
Dim lngLines As Long
Const FMSVersion = "7.0"
Private Sub cmdCancel_Click()
DoCmd.Close
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdNotepad_Click()
Dim x As Variant
x = Shell("write.exe " & Me!txtFileName, 1)
End Sub
Private Sub cmdStart_Click()
Dim fOK As Integer
If Me!txtFileName <> "" Then
If Me!txtTabs <> "" Then
intTabs = 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 3
End If
End Sub
Private 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 DATABASE
Dim tdfTmp As TableDef
Dim fldTmp As Field
Dim qdfTmp As QueryDef
Dim relTmp As Relation
Dim idxTmp As INDEX
Dim cntTmp As Container
Dim docTmp As Document
Dim prpTmp As Property
Dim intCounter As Integer
Dim intBCounter As Integer
Dim intCCounter As Integer
Dim intPCounter As Integer
' Delete the output file
On Error Resume Next
Kill strFile
On Error GoTo fDumpDAO_Err
' Initialize
intFileOut = FreeFile
Open strFile For Output As intFileOut
Set dbsCurrent = CurrentDb()
lngLines = 0
' Write the database info
Call 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 collection
For 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.Fields
Call WriteOutput("TABLE FIELD: " & fldTmp.Name, 2)
If fProps Then Call WriteProps(fldTmp, 3)
Next fldTmp
' Iterate the Indexes collection
For Each idxTmp In tdfTmp.Indexes
Call WriteOutput("INDEX: " & idxTmp.Name, 2)
If fProps Then Call WriteProps(idxTmp, 3)
' Iterate the index fields collection
For Each fldTmp In idxTmp.Fields
Call WriteOutput("INDEX FIELD: " & fldTmp.Name, 3)
If fProps Then Call WriteProps(fldTmp, 4)
Next fldTmp
Next idxTmp
Next tdfTmp
' Iterate the Relations collection
For 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.Fields
Call WriteOutput("RELATION FIELD: " & fldTmp.Name, 2)
If fProps Then Call WriteProps(fldTmp, 2)
Next fldTmp
Next relTmp
' Iterate the querydefs collection
For 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.Fields
Call WriteOutput("QUERY FIELD: " & fldTmp.Name, 2)
If fProps Then Call WriteProps(fldTmp, 2)
Next fldTmp
Next qdfTmp
' Iterate the Containers collection
For 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.Documents
Call WriteOutput("DOCUMENT: " & docTmp.Name, 2)
If fProps Then Call WriteProps(docTmp, 3)
Next docTmp
Next cntTmp
fDumpDAO = True
Close intFileOut
fDumpDAO_Exit:
Exit Function
fDumpDAO_Err:
If fErrors Then
WriteOutput "************** Error: " & Error$, 0
End If
Resume Next
End Function
Private Sub Form_Open(Cancel As Integer)
Me!lblVersion.Caption = "Version " & FMSVersion
Me!txtFileName = "C:\DAO_DUMP.TXT"
Me!chkProperties = True
Me!chkErrors = True
Me!txtTabs = 4
End Sub
Private 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 String
strTabs = Space(intIndent * intTabs)
Print #intFileOut, strTabs & strOut
lngLines = lngLines + 1
End Sub
Private 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 Integer
Dim strSaveErr As String
Dim strName As String
Dim varVal As Variant
Dim prpTmp As Property
For 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 Then
Call WriteOutput(strName & ": " & varVal, intIndent)
Else
If fErrors Then
Call WriteOutput("************** " & strName & ": Error (" & strSaveErr & ")", intIndent)
End If
End If
Next prpTmp
End Sub
You are welcome to extend this add-in to 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 to text files. Additionally, you may want to add the ability to get the structure of non-DAO objects such as forms and reports.
Several Microsoft Jet–specific tools are not fully documented in the books that ship with Microsoft products. Although these tools are not DAO-specific, they can make it easier to develop and maintain DAO applications.
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:
Microsoft Jet contains an undocumented function called ISAMStats, which 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:
| StatNum | Description |
| 0 | Number of disk reads |
| 1 | Number of disk writes |
| 2 | Number of reads from cache |
| 3 | Number of reads from read-ahead cache |
| 4 | Number of locks placed |
| 5 | Number of release lock calls |
See the included add-in (Jetmeter.exe) for an example of how to use this function.
Microsoft Jet implements a cost-based query optimizer 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).
You can use the 32-bit 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.
By now, you should be comfortable with both the importance of understanding DAO and how to use it in your database applications. As the future unfolds, you will be able to leverage your knowledge of DAO in more powerful and advanced database work.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, Microsoft Press, Visual Basic, Visual C++, Windows, and Windows NT are registered trademarks and Visual FoxPro is a trademark of Microsoft Corporation.
Other product or company names mentioned herein may be the trademarks of their respective owners.