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. This is because, unlike Access, these applications do not have an inherent connection to the data source. When used in Access, DAO allows you to manipulate data and schema through an implicit connection that Access maintains to whichever Jet, ODBC, or ISAM data source it happens to be connected to.
As linked tables are a uniquely Jet-specific feature, DAO is quite simply the better alternative for accessing Jet databases. In fact, it is impossible to do so natively using any other data access model.
DAO has evolved right alongside Jet, and has become the best model for accessing and manipulating Jet objects and structure. Because of its tight integration with Jet, DAO also provides much faster access to Jet databases than does ADO or 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 allows you to link (connect) to multiple databases simultaneously.
□ Using the OpenRecordset method's dbDenyWrite option, DAO allows 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 allows 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 allows you to create replica databases that prevent users from deleting records, whereas 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 allows you to set and change Jet options without requiring you to make Registry changes.
□ DAO allows 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 allows 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 allows you to run a separate Jet session, using PrivDBEngine, whereas ADO does not.
□ 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, I think it is safe to say that DAO will be around as long as Jet databases are used.
Was this article helpful?