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.

DAO Pros:

• Fast
• Stable, bug-free code
• Integrates well with Access (Jet) databases.
• Shares the Access connection. Does not open a separate connection to the database when running in Access.

DAO Cons:

• Does not scale well to other databases
• Does not scale well to large recordsets (million+ record queries with lots of business logic can take a long time)
• Has a very deep object model requiring a lot of "." notation
• Does not scale well to web interfaces
• Does not support disconnected recordsets

ADO Pros:

• Scales to virtually all databases that run on a MS platform
• Runs quickly on large recordsets. (DAO can sometimes outperform ADO in that it does not load the entire recordset when first called, but rather loads records incrementally, only as necessary. ADO will yield faster results with functions performed on the entire large recordset.)
• Works great with tables in Access that are really connections to tables or views in other types of databases
• Has some nice methods that DAO doesn't for testing status of recordset
• Has a very shallow object model (basically Connection Command And Recordset)
• Supports disconnected recordsets
• Supports stateless HTTP protocols
• Providers are also available for non-MS platforms such as AS/400
• Supports ANSI-92 DDL query statements such as GRANT + REVOKE statements.
• Can apply Sort and Filter properties 'in place'. Does not require a separate Recordset object to use Sort and Filter.
• Like DAO, forms can be bound to ADO recordsets (requires code)
• Disconnected, In-Memory Recordsets can be constructed on the fly
• Recordsets can be save to and loaded from XML files and Stream objects
• ADO natively supports Recordset Paging through use of the PageSize and PageCount properties (forms have no built-in support for this)

ADO Cons:

• Slightly slower than DAO
• Does not really work with Access 97 and previous versions
• Syntax is more difficult for beginners
• Requires second library for some data definition activities (ADOX)
• In a standard database file (not an ADP) a report cannot be bound to an ADO recordset
• Filter method does not allow use of keyword OR on anything but the top level
• Filter method fails on recordsets containing more than 500 fields
• Batch updating in ADO is not compatible with forms bound to ADO recordsets (batch modified flags are cleared on AfterUpdate event)
• Forms bound to ADO recordsets cannot use the following form-level properties: Filter, OrderBy, RecordSource
• Default sorting and filtering menus/options are not compatible with forms bound to ADO recordsets

Some more things to consider. Since Access 2003, DAO has returned to be the default library in Access. This includes Access 2007. The ADO library is no longer referenced when creating a new .MDB or .ACCDB in A2007. In Access 2007 Microsoft has included more features in DAO (ACEDAO) to allow you to work with the new features of the enhanced database engine (.ACCDB). Most notably, these features are:

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.

Attachment fields
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.

Conclusion:

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:

Rob Cooper
Ricky Hicks
John Mishefske
Brent Spaulding
Chris O'Brien
And other UA members too numerous to list.

This post has been edited by niesz: May 21 2012, 12:39 PM