See Also: Wiki: Choosing Between DAO and ADO

One great feature of Access is the ability to choose between Data Access Objects (DAO) or ActiveX Data Objects (ADO). Though DAO remains the de facto standard for data access, ADO can be used in same project to supplement additional functionality for where DAO may be a poor fit. ADO supports powerful tools such as disconnected recordsets, local cursor engine, and ability to update data even against sources that normally would be non-updatable such as stored procedures.

However, using ADO requires considerably more handling and discipline. The intent of the article is to discusses known issues and workarounds to supplement the documentation on ADO which usually are written for general use and not specifically for use within in Access.

Access' ADO-specific properties

Access objects has some properties that are specific to ADO and will not work correctly with DAO even though it may seem to be available all times.

Form Properties

UniqueTable

ResyncCommand

Known Issues

Subform linking does not work

Though an ADO recordset can be bound to a form, even a form that's a SourceObject of a subform container control, it does not work with the LinkChildFields and LinkMasterFields. A workaround is to manually maintain the filtering by using the parent form's Current Event.

RecordsetClone property is not available

Use Form.Recordset.Clone instead.

Call .Update method on recordsets

It has been observed that if an ADO recordset is bound to a form, and data has been changed but the .Update method hasn't been called, this may crash Access. It is best to be explicit with the .Update call after modifying a bound recordset.

Apply Sort or Filter to a bound recordset

If you specify either Sort or Filter to an ADO recordset bound on a form, the form will not automatically display the changes made to sorting or filtering. Requerying or Refreshing will not work either. To update the visible changes, here is an example for using inside a form's module:

It is not possible to use the forms Filter or Order By properties when it is bound to an ADO recordset. Also, the default shortcut menus and ribbon menus for filtering and sorting do not have any affect on ADO recordsets and my produce an error if a users attempts to use them.

Applying both Sort and Filter return only 100 records

One can use either Sort or Filter and get as many records but once both are applied, only 100 will be returned. A workaround is to requery the source for one of operation and use other.