Dao Vs Ado - The Latest Viewpoints.
Aug 22 2006, 02:21 PM
From: Cincinnati, Ohio, USA . . . ><((((°>
DAO or ADO? Which should I use? This is a commonly asked question, especially in view of the changing development paths that Microsoft has taken over the last few years. Which is best for your application? The answer may not be as straightforward as you may think!
Let's start by defining a few things:
DAO - Data Access Objects
Allows VB applications to talk to a database (the JET Engine) via ODBC. DAO was Microsoft's first object oriented solution for the manipulation of databases using the Jet Database Engine.
ADO - ActiveX Data Objects
Allows VB/Other Web Tools (Browsers) to interface with different kinds of data sources. ADO is a more recent Microsoft Data Access technology.
ACEDAO - Access Engine Data Access Objects
Allows VB applications to talk to a database (the Access Database Engine). ACEDAO is Microsoft's enhanced version of DAO, built specifically for working with the new ACE database engine in Access 2007.
Is newer always better?
In the early days of Microsoft Access the choice was simple. DAO was the only option. It was built to talk easily and efficiently with the built-in Jet Engine of an Access application. Since Access 2000, things started to get a little murky, as ADO was packaged as the default object model. Why did Microsoft change their paradigm? As newer technologies developed, ADO was seen as the preferred method to connect to disparate sources (SQL Server, Oracle, XML, etc.).
In order to fully utilize all the rich features of these newer sources ADO has more things going on "under the hood". This tends to slow some things down if all you need are the basics. If you are dealing solely with an Access (Jet) database, then DAO will perform faster than ADO and should be your tool of choice for your application. If you are using Access as a front-end for connecting to another source that can benefit from the newer ADO features, then ADO is for you.
Listed below are some pros and cons for each. This should not be considered as an exhaustive list.
Multi-value lookup fields
A multi-value lookup field is a field that can store multiple related values for a given record in an embedded recordset.
The database engine supports a new data type called Attachment that can be used to store files in a database. The files are compressed for storage unless the file being added is already compressed. There is also a new Attachment control in Access 2007 to support this data type.
Append only memo fields
Memo fields support a new property called AppendOnly that is used to track column history for data changes to the field. Each change made to an append only field is saved in the database and can be retrieved using a new method on the Access.Application object called ColumnHistory.
DAO is not going anywhere. Microsoft is committed to supporting it well into the future. If your application is purely a Microsoft Access database, then DAO should be the obvious choice. If you need some advanced recordset manipulation features and are connecting to a supportive outside source, then ADO is the ticket. Also, it should be noted that there is nothing wrong with mixing these two methods within the same project.
For a more detailed discussion of when to use DAO vs ADO please consult the following Wiki article:
Choosing between DAO and ADO
I would like to thank the following people for their input into this FAQ:
And other UA members too numerous to list.
This post has been edited by niesz: May 21 2012, 12:39 PM
Reason for edit: Added additional pros/cons and Wiki link
|Go to Top · Lo-Fi Version||Time is now: 20th May 2013 - 08:41 PM|