ADO vs. DAO (or A97 vs A2K)

ADO vs. DAO (or A97 vs A2K)

A problem encountered by people taking their first steps in programming with Access 2K is the fact that they copy code written for previous versions, and then encounter error messages such as "User type not defined."

This is due to the fact that by default Access 2K uses the ActiveX Data Object (ADO) Object Library, whereas previous versions used the Data Access Objects (DAO) Object Library. Many of the Objects in the DAO Object Library do not appear in the ADO Object Library, and even where they do appear, the methods and properties are often different.

The steps to ensure that A97 code will work in A2K is:

Set a reference to DAO in the database (note that as references are database specific, you will need to do this in each database). From the database window, press Ctl-G to bring up the debug window, and then select Tools|References. Add a reference to the appropriate version of DAO, which is currently 3.6.

You might want to consider removing the reference to ADO if you do not intend to use any Objects in this Library. There are several advantages to doing this. Firstly, the more references that there are in a database, the more chance there is of any of them becoming "missing", which will cause code in your database to stop working properly, regardless of which Object Library the function belongs to. Secondly, you won't need to disambiguate declarations.

If you leave a reference to the ADO Object Library in your database, then you will need to take care when declaring Objects that belong to both Object Libraries, such as Recordset:

Dim rsADO As ADODB.Recordset
Dim rsDAO As DAO.Recordset

If you don't do this, Access will probably allocate the Object to the highest ranked reference, with possibly unexpected results. So the best thing to do is to play it safe, and tell Access that it does belong to a certain Library.

Top

Last modified at 06/06/2006 13:55:19