Revision as of 19:02, 21 May 2012
Choosing between DAO and ADO
From Access 2000 and onward, Access has supported two different data access libraries, Data Access Objects (DAO) and ActiveX Data Objects (ADO). It is a common question for budding Access developers to ask which libraries they should use. There are several considerations but the most important thing to remember is that it is not an either/or proposition. It is perfectly possible and possibly more desirable to mix both libraries in same application and thus leverage the best of both worlds. The article focus more on when and why should a particular technology be used.
Note: As of Access 2007 and onward, the default data access library is named "Microsoft Access Database Engine Object Library" or ACEDAO. Though this has different name, it is merely a newer version of DAO with support for new functionality introduced in those versions. Unlike the case between ADO and DAO, one cannot have both ACEDAO and DAO referenced in the same application; one or other must be chosen. Fortunately, the rest of article is relevant to both so for the rest of article, DAO will be used to refer to both DAO and ACEDAO.
It should be also noted that DAO has supported a different mode, ODBCDirect, which has been since deprecated and thus is not considered in this article.
Defaults and Future
ADO was introduced to Access originally as a replacement for DAO library and depending on which version the MDB was created in, the file may reference either DAO, ADO or both. As of Access 2007 and onward, ACEDAO is now the default reference and it is necessary to manually add ADO references to employ its functionality. Though ADO was meant to replace DAO, Microsoft has since reversed the position and made DAO the blessed data access library for Access. Furthermore, Microsoft has blessed ADO.NET, which is entirely different data access library with little in common to ADO other than the three letters. Thus, it is unlikely that ADO will receive any active development. However, Access does not support ADO.NET, which uses .NET framework so ADO.NET is not a choice for Access at this time. Even without any active development, ADO has some compelling features for Access that merits its inclusion even in current development.
Recordsets in General
The difference in ADO and DAO is most felt when using recordset object, whether as a part of automation within VBA, bound to Forms, Reports or controls that has RowSource property. It should be noted that even when Access objects do not contain VBA and is bound to a query or table, a Recordset is used implicitly. However, when binding Access objects through the user interface, it is always done via DAO in MDB and ACCDB. On the other hand, ADP implicitly uses ADO recordsets. To bind a Recordset of the other library to an Access object, it is necessary to use VBA routine to perform the binding.
Chatty versus Chunky
Before we delve into specifics of both libraries, it may be useful to first examine the two general models used in data access. For this discussion, we'll use the term 'chatty' and 'chunky'. The term refers to how a Recordset would communicate with the data source. With a chatty recordset, the client maintain an active dialogue with the data source to keep the data in sync. Typically, this means the client downloads the keys needed to scroll and execute several subsequent statements against the server to fetch one or few rows in batch on demand. On the other hand, a chunky recordset simply obtain the full set and there is no further communication with the data source afterward. Of course, there are middle ground in between but keeping in mind those two model can be very helpful in determining what is most appropriate type of Recordset to use for a particular scenario.
It's also helpful to consider the effects this would have on the data source. With a chatty recordset, the initial statement that typically obtains the keys or unique indexes needed to scroll the recordset is certainly much cheaper than obtaining the whole data set in one go and fits the scenario where browsing is desired much better. The users need not wait as long for initial loading and they can be assured they will be viewing current data. On the other hand, if there are several users using same data source and they all use chatty recordsets, there is much greater potential for lock contention and burdening the server as it has to respond to several small requests than few big requests. Again, it's not an either/or proposition - both can be used to great advantage and it is developer's responsibility to ensure the correct model is selected for a given application.
Types of Recordset
The table below enumerates all available types of Recordset for both ADO and DAO libraries, ordered from lightweight to expensive.
* Table-Type Recordset always lock the edited row and cannot be used against linked tables, even Jet/ACE tables so it is practically impossible to share a table type recordset.
Both libraries' Recordset implicitly uses cursors to navigate through the Recordset, though DAO's literature may not use the phrase 'cursor' but the concept is still present. Also, it should be noted that the cursors are distinct from the cursors used in RDBMS. Many experienced RDBMS developers tend to avoid using cursors with the stored procedures where possible and processing the data as a set and understandably so. However, cursors as used in either data access libraries are entirely different concept from the SQL cursor and thus should not be confused.
ADO provides the developers a choice to use server-side cursor or client-side cursor. However, the types of Recordsets available are restricted- in fact, a client-side cursor allows only for one type: Static. Using client-side cursor basically means the ADO handles the navigation and updates. With server-side cursor, the exact nature will depend on implementation of the provider. It may be the provider itself that acts as the server providing the cursor functionality, or it may make use of SQL cursor supported by the RDBMS to provide the service. Thus it may be necessary to consult the documentation on the specific provider being used in one's application to fully understand the implication of using a server-side cursor.
With DAO, no such choice is available; everything is managed client-side, by the database engine, even for linked tables. Fortunately, the database engine is very efficient at managing the data and because Dynaset recordset is the default, it tends to adapt the chatty model and processing the rows in batches. However, the efficiency is very much influenced by the SQL -- if the developer issue a poorly formed SQL statement, the engine may be forced to download additional data, even possibly the whole table to satisfy the records. Thus the developers should be judicious in the query-writing to enable the engine to hand off as much processing as it can to the backend.
Locking behaviors and Options
Both DAO and ADO provide support for customizing a Recordset's behavior. In case of locking, both DAO and ADO equally support all modes of locking, ReadOnly, Optimistic and Pessimistic. However, DAO's locking is completely ignored when used against a ODBC-linked table, deferring to the data source's default locking behavior. In case of ADO, the locking behavior is available as long the provider being used supports this. It should be noted that for unsupported behaviors, ADO in conjecture with the chosen provider, will silently substitute the specifications with the closest valid specifications which may not be always desirable. Thus when using ADO, it is strongly encouraged to read up on the documentation for chosen provider to understand the specific behaviors that are available with this provider.
DAO supports the concept of denying others the read or write access to the data source for which Recordset is opened against. Such functionality is not available with ADO, though there may exist workarounds such as using the server's native SQL to proceed the locking as exclusive to achieve the same effect.
Disconnected and In-Memory Recordsets
A powerful feature of ADO is the ability to create Recordsets and then disconnect the recordset from the data source. This opens up for many possible applications including but not limited to:
To do something analogous in DAO would usually necessitate creating a temporary table which requires I/O and modifying an Access file, which introduces several possible complications.
An ADO Recordset can be disconnected by using the following command: Set rst.ActiveConnection = Nothing
ADO also allows creating an In-Memory Recordset which is basically a recordset that is created entirely in memory without any source to build or define the structure. This can be very useful for scenarios where you need to allow users to create their own structure for their data processing, but again without the overhead of disk I/O and temporary database that DAO would require.
Batch updating of an ADO recordset bound to a form is not possible because Access clears the edited flag on the forms AfterUpdate event. Having said that, changes made to records in a disconnected recordset can be processed by creating a new connected recordset and copying all records that have been changed. This works best if you use a DateModified/DateEdited field that gets updated on the Form's BeforeUpdate event allowing you to easily compare two recordsets to see which records have been changed.
SQL Syntax and PassThrough
DAO binds tightly with Access database engine and for that reason, DAO Recordsets heavily depends on the engine to create and manage the Recordset. Because it is the engine that does the processing, it expects Jet SQL, and cannot process different dialects of SQL even if the Recordset may be opened against a linked table and the source of that table would consider the SQL to be valid. For this reason, DAO supports the notion of 'PassThrough' which allows developers to pass the SQL statement directly to the data source without the database engine parsing the SQL. This can be a powerful feature but it's limited by the fact that PassThrough are non-updatable.
With ADO, everything is "passthrough" and thus SQL syntax must be valid for that backend, rather than using Jet SQL parsed by the database engine into ODBC SQL. Under right conditions it is possible to create an ADO recordset that is a resultset of a stored procedure and have it be fully updatable. For scenarios where a complex requirements is best served by using stored procedures, ADO can be useful in providing an interactive interface against the stored procedures instead of having to resort to temporary tables or complex form/subform setup to support the same functionality in DAO.
In case of Access Reports, the matter is quite straightforward. Because Reports are intended to be read-only, using chunky recordsets usually is best choice. Furthermore, if the Report is against a linked data source, a PassThrough Query makes for an excellent choice for driving the Report. As it is provided via the Access interface, there is little reason to incur the extra time and effort of writing VBA routines to bind a Report to different recordset which also is not possible anyway.
Under some circumstances where a report against large amount of data create unnecessary lock contention, it may be desirable to use chatty recordsets, but even so there are means of using chunky recordsets that do not require locking which also can rectify the situation.
Unless you are using an Access Data Project, reports cannot be bound to ADO recordsets. You will either need to use DAO or else dump the ADO recordset to a temporary table and then bind the report to that temporary table.
As it is typical that forms are intended to be presented to the user for interactive uses, including making changes, it's a common requirement that the Recordset be updatable. For this reason alone, ForwardOnly and Snapshot can be immediately ruled out.
Generally speaking, it is typically necessary that the SQL statement must create a result set in such way that a row in result set can be traced back to the original row in the source. This applies whether we're using DAO or ADO. Thus, this typically rules out any queries that does aggregating, DISTINCT, joins in different directions, UNION among others. Several developers find it simple to correspond a form to one table, using subforms to represent the related tables. This is not to suggest that a form can't be bound to multiple-table recordset and be updatable, only the conditions makes it generally unlikely that it's useful to do so.
It should be noted that DAO supports a subtype of Dynaset, Dynaset (Inconsistent Snapshot) which is useful in binding a table that has a one-many relationship and allowing one to update all columns from both tables. In a Dynaset-type Recordset, it is required that when one-side table is dirtied, it must be saved before the columns representing the many-side table can be dirtied, which may be problematic for scenarios where business rule does not allow a one-side records with no child records for instance.
As mentioned earlier, ADO allows native SQL dialect and thus can support stored procedures or table-valued functions, provided the same one-to-one rule still apply. The ability to bind a form to a stored procedure and still be updatable can be very powerful and convenient feature by allowing developers to move the processing to server and leverage the full power of the backend.
ADO's ability to disconnect recordsets also open up the potential to create a form-subform and batching the edits collectively so there is a measure of guarantee that the changes will be consistent. This need not involves the actual Begin/Commit/Rollback which unfortunately is problematic against a bound form for both libraries and thus is not fully ACID-compliant but it is an alternative that does not require so much effort. Validation can be handled within the parent form, by Unload event for example before it sends all changes back to the data source.
Continuous Form/Datasheet and Unbound columns
A common problem Access developers face in using a continuous form or datasheet is when the requirement call for some kind of controls that do not actually relate to the data. A common example is the need to allow the users to view records and select a unknown number of records for further processing, maybe to delete or report upon or the like. On a single form, that can be easily done with an unbound control, but such thing will not work on a continuous form or datasheet. Under DAO, it is commonly solved by making use of temporary tables with additional columns to act as the flags and thus bind the control to those new columns.
With ADO, disconnected recordsets can be used to provide fake updates and thus create a SQL statement using static columns to provide additional columns to bind to the continuous form. Because it is disconnected, there is no issue of updates being restricted. Of course, such use do not actually permit actual updates back to the source without additional steps. For the requirement of flagging a number of records, this is usually sufficient since the flags usually would be discarded once the user is done with it.
In some scenarios, the developer may want to construct a form that display additional information from other tables but allow updates to only one table bound to the form. In theory, such thing should updatable but in practice it is not always possible with DAO. To maintain an updatable recordset, it may be necessary to create a user-defined functions in the query to perform lookups to other tables. For one or two columns, this should work reasonably well enough, but when there are more display columns, the process can get quite expensive and become inefficient compared to a SQL statement that actually join the tables.
ADO supports the concept of UniqueTable which allows the developer to specify which table should be eligible for updates and provided that there still is a one-to-one ratio between the resultant row and source row, will be updatable. This enables the developer to use an optimized SQL statement without losing the updatability.
User-driven filtering and sorting
Both libraries support filtering and sorting. However, some Access developers has avoided using the native functionality provided in Access due to its quite expensive overhead. It is common to filter and/or sort by issuing SQL statements. In many cases, this is usually faster than using either libraries' functionalities. However, this means more network traffic and load on the server. For scenarios where this is not acceptable, the developers may want to ensure that filtering and sorting are done client-side with a snapshot of the data.
With ADO, disconnected recordsets can be used in conjecture with filtering/sorting to enable the user to manipulate data for viewing/browsing without any concern for the load being placed on the server or consuming precious network bandwidth. One limitation is that ADO recordset filtering and sorting cannot be handled by the form properties Filter and Order By or by the default right-click menus and ribbon menus provided in the non-runtime version of Access.
Another limitation is that conditions within ADO filters can only contain the OR keyword at the topmost level. For example:
Acceptable: rst.Filter = "Country = 'US' OR Country = 'UK'"
Produces Error 3001:
rst.Filter = "(Country = 'US' AND Region = 'NorthEast') OR (Country = 'UK' AND Region = 'Scotland')"
There are certain controls for which Recordsets can be assigned, typically Combobox and Listbox. Because they are used to display values, there is generally no need for updatable recordset and thus it is usually desirable to use the lightweight type for those controls. This remains true even for Combobox which supports NotInList event and allows addition of new entries since NotInList does a implicit Requery and thus is not hampered by the non-updatability of the Recordset. Thus, it generally is desirable to ensure that DAO's Snapshot and ADO's Static recordset is used. However, for controls that reference a large amount of data, the chatty model may actually provide better performance so this should be kept in consideration.
Sometime the controls is used to assist in navigation or search of the records that is a part of the form's Recordset. Rather than creating a separate query, one can re-assign form's Recordset to the control and thus save on the round trip and overhead. Such operation is supported by both libraries.
Pros vs Cons
Listed below are some pros and cons for each. This should not be considered as an exhaustive list.
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.
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.
You can bind a form to an ADO recordset based on a stored procedure and/or table-valued function, and if all updatability rules are met, it can be updatable. That's not possible using DAO passthrough queries.
Likewise, you can use UniqueTable to also make a multi-table query updatable. This is partially true with DAO recordsets but there are few cases where multiple table query cannot be easily updated even though the update would map to only one record on one table.