Visual Basic Programmer

Microsoft Access Database Programmer

Database Retrieval Methods: DAO or ADO?

There are four options available for retrieving or modifying Database objects, when using Visual Basic (VB6) or Visual Basic for Applications (VBA):

• DAO (Data Access Objects): Allows Visual Basic code to communicate with a Microsoft Access database. The DAO object model was designed specifically for the Microsoft Jet database engine – i.e. Microsoft Access Databases.
• ACEDAO (Access Engine Data Access Objects): An enhanced version of DAO used to communicate with an Access 2007 database.
• ADO (ActiveX Data Objects): Allows Visual Basic code to communicate with any database. The ADO object model was designed to manipulating all data engines, for example Microsoft SQL Server, Excel spreadsheets or Access Databases.
  
• ADO.Net: The latest version of ADO that is used with VB.Net and ASP.Net to communicate with any database, and especially on the Web.

The DAO Pros

• Fast – it is optimised for the Access Jet Engine
• Stable, bug-free code
• Provides specific Microsoft Access functionality
• Opens a single connection to the database when a shared connection is required

The DAO Cons

• Does not scale well to other databases
• Does not scale well to large Recordset
• Does not scale well to web interfaces
• Does not support disconnected Recordsets

The ADO Pros

• Scales well to Microsoft SQL Server
• Runs quickly on large Recordsets
• Has methods for testing the status of the Recordset
• ADO uses a simpler and more flexible object model than DAO
• Supports disconnected Recordsets
• Provides Sort and Filter facilities
• Can be used with Visual Basic as well as Visual Basic for Applications

The ADO Cons

• Not as efficient with Microsoft Access as the highly tuned DAO

Notes on ADO and DAO

• Both DAO and ADO can be used within the same project.
• Use DAO when the required functionality is easier to create than using ADO or ADOX.
• ADO is the preferred method when linking to databases other than Access, i.e. a local SQL Server database, an SQL Server database on a Website, or Excel.
• DAO will eventually be replaced by ACEDAO. It is unlikely that ADO will ever be replaced by ADO.Net.