Data Access Objects

DAO is the programmatic interface between VBA and Access database engine databases, ODBC (Open Database Connectivity) data stores, and installable ISAM (Indexed Sequential Access Method) data sources, such as Excel, Paradox, dBase, and Lotus 1-2-3.DAO was first released as a part of Visual Basic 2.0 and later released with Access 1.0 in November 1992. Over the years, many changes have been made to both DAO and to the Microsoft Jet database engine to reflect technologies at the time. Support for 32-bit operating systems, ODBC data sources, and Unicode languages were all included in current versions of DAO.

DAO 12.0 is the latest version, shipped with Access 2007, and is the version used by the new ACCDB file format. This new release was written for use with the Access database engine, which is an updated version of the Microsoft Jet database engine and is 100% compatible with Jet. The new features added to DAO and the Access database engine include new objects and properties that support multi-value lookup fields, a new Attachment data type, append-only memo fields, and database encryption using the database password. The filename for the Access database engine is ACECORE.DLL. (With ACE in the name, you may occasionally see the Access database engine referred to as ACE in newsgroups and blogs.)

Why Use DAO?

Visual Basic programmers highly recommend ADO as their preferred object model for accessing databases. Although ADO is an excellent model with its own unique benefits, in the context of Access databases, it doesn't have the benefit of native database connectivity, which is where DAO has the distinct advantage.

Applications written in other programming languages, such as Visual Basic, Delphi, and the like, must explicitly connect to the data source they intend to manipulate, and they must do so every time they need to manipulate the data or underlying schema. That's because, unlike Access, these applications do not have an inherent connection to the data source. When used in Access, DAO enables you to manipulate data and schema through an implicit connection that Access maintains to whichever Access database engine, ODBC, or ISAM data source it happens to be connected to.

Because linked tables are a uniquely Access-specific feature, DAO is quite simply the better alternative for accessing Access databases. In fact, it is impossible to do so natively using any other data access model.

DAO has evolved right alongside Jet and the Access database engine, and has become the best model for accessing and manipulating Access database engine objects and structure. Because of its tight integration with Access, DAO also provides much faster access to Access databases than does ADO or the Jet Replication Objects (JRO). This may all sound like marketing hype, but to qualify the advantages of DAO over other models, consider the following:

• ADO connections can only be applied to one database at a time, whereas DAO enables you to link (connect) to multiple databases simultaneously.
• Using the OpenRecordset method's dbDenyWrite option, DAO enables you to open a table while preventing other users from opening the same table with write access. The ADO Connection object's adModeShareDenyWrite constant operates at connection level - not at table level.
• Using the OpenRecordset method's dbDenyRead option, DAO enables you to open a table while preventing other users from opening the table at all. The ADO Connection object's adModeShareDenyRead constant can only be set at connection level.
• You can create users and groups in DAO, but not in ADO, because you can't specify the PID (Personal IDentifier) in ADO.
• You can secure Access objects (such as forms, reports, and so on) in DAO, but not in ADO, because there are no suitable ADO constants to specify permissions for execute, read changes, and write changes.
• You can dynamically link an updatable ODBC table in DAO, but not in ADO.
• DAO enables you to create replica databases that prevent users from deleting records; JRO does not.
• In DAO, you can return information about Exchange and Outlook folders and columns using the TableDef and Field Attributes properties. ADO does not pass this information on.
• Using the DBEngine's GetOption and SetOption methods, DAO enables you to set and change Access database engine options without requiring you to make Registry changes.
• DAO enables you to create, change, and delete custom database properties.
• You can force the database-locking mode with the DAO.LockTypeEnum constants against CurrentDb, but you can't do the same thing in ADO using ADO.LockTypeEnum against CurrentProject.Connection.
• Using AllPermissions properties, DAO enables you to retrieve an object's implicit permissions, whereas ADO doesn't have an AllPermissions property, forcing you to enumerate the groups of each user.
• DAO enables you to run a separate session of the Access database engine, using PrivDBEngine; ADO does not.
• DAO enables you to create multi-value lookup fields using new complex data types. A multivalue lookup field is a single field that can store multiple values in an embedded recordset. You'll explore this new field type in more detail later in this tutorial.
• DAO enables you to create and insert data in an Attachment field. Attachment fields are a new data type in the Access database engine and will be examined in more detail later in this tutorial.

The current version of DAO is a very mature, well-documented, and easy-to-use object model for accessing database services. You can use DAO from any VBA environment such as Word, Excel, and so on, and a variety of other programming languages such as Visual Basic, FoxPro, and C++.

Finally, it's fairly safe to say that DAO will be around as long as Access or Jet databases are used.