Access Database Programming
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.
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
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
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
ADO Cons
- Not as efficient with Microsoft Access as the highly tuned DAO
Notes
- 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, and ADO by ADO.Net.
|