Access Database Retrieval Methods: DAO or ADO or ADO.Net?
There are four Microsoft data access technologies available for retrieving or modifying Database objects (i.e. Tables and Records).
These can be used with Visual Basic.Net (VB.Net) or the Visual Basic for Applications (VBA) language.
The options vary from the very old, to the latest technology:
- 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 or later 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 to communicate with any database – especially required for the Web.
The advantages and disadvantages of DAO
- 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
- 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 advantages and disadvantages of ADO
- 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
- Not as efficient with Microsoft Access as the highly tuned DAO (but hardly noticeable)
The advantages and disadvantages of ADO.Net
- Disconnected TableSets have a very small overhead
- Minimises database connections
- Handles Microsoft Access Back-End databases very efficiently
- Essential for retrieving Website data
- Not advisable for use with VBA code
Comments on the use of 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.
- 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.
- Use ADO if an upgrade to SQL Server is remotely possible
- DAO will eventually be replaced by ACEDAO.