MS-Access / Getting Started

---

DAO Architecture

The first (and older) of the two models you can use to fetch data and examine or create new data objects is the DAO model. This model is best suited for use within Access desktop applications (.accdb and .mdb) because it provides objects, methods, and properties specifically tailored to the way Access and the ACE work together. To use this model, you must ask Visual Basic to load a reference to the Microsoft Office 14.0 Access Database Engine Object Library. To verify that your project includes this reference, open any module in Design view and click References on the Tools menu. If you don't see the check box for this library selected at the top of the References dialog box, scroll down the alphabetical list until you find the library, select its check box, and click OK to add the reference. Access 2010 creates this reference for you in any new database that you create.

Absolutely not! First, you need to know a bit of history. Beginning with version 9 (Access 2000), the Access development team introduced ADO to make it easier to work with SQL Server or other server databases as the data store for Access applications. ADO was touted as the "new direction" for data engine object models because it was designed to be more generic to work with different databases. Access 2000 also introduced the project file format (.adp) that lets you create an Access application linked directly to a database in SQL Server. Both Access 2000 and Access XP (2002) provided a default reference to the ADO library in new database, and you had to add the DAO library if you wanted to use it. Microsoft also declared DAO "stable" (read: no new enhancements) and began distributing the Access JET database engine as part of Microsoft Data Access Components (MDAC) that you install with your operating system- Microsoft Windows 98, Windows 2000, Windows XP, Windows Vista, or Windows 7. And so, the developer community began to think that DAO was "dead."

But DAO in many cases really works better if you're building a desktop application. DAO gives you direct access not only to all your table and query definitions but also forms, reports, macros, and modules. Also, the record source for all forms and reports creates a DAO recordset, so it doesn't make sense to try to use the entirely different ADO recordset object in your code. As of Access 2002, you can assign a recordset object you open in code directly to the Recordset property of a form. But if you're using an ADO recordset, features that you expect to work-such as updating across a join or autolookup when you set a foreign key-don't work correctly. In short, DAO was designed to work best with Access desktop applications.

When Microsoft stopped providing DAO as a default reference in new databases, many in the developer community pointed out to Microsoft that this really wasn't a good idea for desktop applications. Microsoft listened to its users and changed the default library back to DAO in Access 2003. However, the Access development team couldn't plan any major enhancements because the JET engine had become part of Windows.

For Access 2007, the development team created its own new version of the JET engine-now called the ACE. ACE includes the new features to support the Attachment and the Calculated data types, as well as multi-value fields, and it also supports all the features of the old JET engine, but uses an enhanced version of DAO. So no, DAO is not dead-it in a sense has been reborn in the new database engine for Access 2007 and Access 2010.

The Application object's DBEngine property serves as a bridge between the application engine and the Access Database Engine. The DBEngine property represents the DBEngine object, which is the top-level object in the DAO hierarchy

The DBEngine object controls all the database objects in your database through a hierarchy of collections, objects, and properties. When you open an Access database, the DBEngine object first establishes a Workspaces collection and a default Workspace object (the first object in the Workspaces collection). If you are opening a secured database created in the prior version format (.mdb, .mde) and your workgroup is secured, Access prompts you for a password and a user ID so that the DBEngine can create a User object in the Users collection and a Group object in the Groups collection within the default workspace. If your workgroup is not secured, the DBEngine signs you in as a default user called Admin.

Finally, the DBEngine creates a Database object within the Databases collection of the default Workspace object. If your prior version format file is secured, the DBEngine uses the current User and/or Group object information to determine whether you're authorized to access any of the objects within the database.

After the DBEngine creates a Database object, the application engine determines whether the database contains any potentially untrustworthy objects. Any database containing tables, queries, macros, or Visual Basic code is deemed potentially untrustworthy. If the database is signed with a certificate that you have accepted as trustworthy or the database resides in a trusted location, the application engine enables all code. If the database is not trusted, the application engine displays a security warning message and provides the option to enable the content in the database.

Next, the application engine checks the database's application options to find out whether to open a display form, load an application icon, and display a title or to use one or more of the other application options. You can set these options when you have your database open by clicking the File tab on the Backstage view, clicking Options, and clicking the Current Database category in the Access Options dialog box. After checking the application options, the application engine checks to see whether a macro group named Autoexec exists in the database. If it finds Autoexec, the application engine runs this macro. In versions 1 and 2 of Access, you'd often use the Autoexec macro to open a startup form and run startup routines. In Access 2010, however, you should use the application options to specify a display form, and then use the event procedures or embedded macros of the startup form to run your startup routines.

You can code Visual Basic procedures that can create additional Database objects in the Databases collection by opening additional .accdb files. Each open Database object has a Containers collection that the DBEngine uses to store the definition (using the Documents collection) of all your tables, queries, forms, reports, macros, and modules.

You can use the TableDefs collection to examine and modify existing tables. You can also create new TableDef objects within this collection. Each TableDef object within the TableDefs collection has a Fields collection that describes all the fields in the table, and an Indexes collection (with a Fields collection for each Index object) that describes any indexes that you created on the table. Likewise, the Relations collection contains Relation objects that describe how tables are related and what integrity rules apply between tables, and each Relation object has a Fields collection that describes the fields that participate in the relation.

The QueryDefs collection contains QueryDef objects that describe all the queries in your database. You can modify existing queries or create new ones. Each QueryDef object has a Parameters collection for any parameters required to run the query and a Fields collection that describes the Fields returned by the query. Finally, the Recordsets collection contains a Recordset object for each open recordset in your database, and the Fields collection of each Recordset object tells you the Fields in the recordset.

To reference any object within the DAO model, you can always start with the DBEngine object. If you want to work in the current database, that Database object is always the first database in the Databases collection of the first Workspace object. For example:

Dim dbMyDB As DAO.Database
Set dbMyDB = DBEngine.Workspaces(0).Databases(0)

Access also provides a handy shortcut object to the current database called CurrentDb. Therefore, you can also establish a pointer to the current database as follows:

Set dbMyDB = CurrentDb

Note: zIn one of the examples at the end of this tutorial, you'll learn how to create a new TableDef object and then open a Recordset object on the new table to insert rows. You can find code examples in the Conrad Systems Contacts application that manipulate objects using both DAO and ADO.