By Tom Wickerath
Avoid the problems with the ADO and DAO libraries.
Access 2000 and 2002 do not include a reference, by default, to the DAO 3.6 Object Library. Instead, Microsoft has included the ADO 2.1 Object Library as the default library for data access in Access 2000 and 2002. This means that if you create a new database in Access 2000 or 2002 and then use VBA code which includes the commonly used Database object, the code will fail. The Database object is not the only object where this problem is likely to occur. TableDef, QueryDef, Workspace, User, Group, and Container objects, as well as their Collection objects, are also commonly used in VBA code to customize database applications. However, without a reference set to the DAO object library, any code that uses these objects will also fail. Microsoft finally listened to the screams of its customers on this issue, so the DAO library is returned to being a default checked reference in new databases created with Access 2003. By default, the DAO reference has a higher priority than the ADO library in Access 2003.
To add the DAO library reference to your Access 2000 or 2002 database application, open the VBA Editor (press <ALT><F11>), then click on Tools > References... You should see the following:
Scroll down the list until you find an entry that reads "Microsoft DAO 3.6 Object Library," then select the check box next to it. Access 2000 and later versions should use DAO version 3.6 to take advantage of the features of the Jet 4.0 database engine. Access 97 should use DAO version 3.51, which handles data access to the 3.x versions of the Jet engine.
Click on the "OK" button to dismiss the References dialog. When you open it up again, you should see the following:
If you click on "OK" to dismiss the References dialog and then use the debug menu to compile your code (Debug > Compile <DatabaseName>), you will find that your code compiles okay. But you will likely have a run-time error when your code actually runs, unless you take the time beforehand to "disambiguate" your code. You will have just discovered how reference priority can be an issue, since both the ADO library (aka "Microsoft ActiveX Data Objects 2.x Library") and the DAO library contain several objects with the same name, such as the Recordset object. Without disambiguation, you'll get an ADO Recordset object if the ADO library is higher in priority, instead of a DAO Recordset object. You can use the "Priority" button to move the DAO library above the ADO library, and then your code should not only compile, but it should run as before without a run-time error.
You should always disambiguate your code to avoid reference priority issues. The following code is an example of how to disambiguate a Recordset variable:
Dim db As DAO.Database
Dim rs As DAO.Recordset or
Dim rs As ADODB.Recordset
(Actually, you don't need to disambiguate the first line of code shown above, since the ADO library does not include "Database," but it doesn't hurt to do so.)
One of the most common errors indicative of a reference priority issue is Run-time error 13 on a line of code that attempts to set a Recordset variable, such as the following code example, where "db" was declared as a Database object variable and "rs" was declared as a Recordset object variable, but without the disambiguated DAO Recordset object declaration shown in the example above, i.e.:
Dim rs As Recordset
Set rs = db.OpenRecordset("Table1", dbOpenSnapshot)
If you are wondering which objects should be disambiguated in VBA code when a database application references both the DAO and ADO libraries, I have a few files included in a Zip archive that you may download here. I have included an Access database which contains a table storing the name of each member of the DAO 3.6 Object Library and the ADO 2.1 Object Library, including the hidden members. The database also contains a query named "Find duplicates for tblADO_DAOObjects." Object names that are present in the recordset of this query should be disambiguated with either ADODB or DAO as a part of the declaration statement in VBA code, so that one does not have to worry about object library priority issues. I have also included an Excel spreadsheet listing all of the members of the DAO 3.6 and ADO 2.1 object libraries, including the hidden members, for handy reference.
Issues with the DAO library when
converting Access 97 to more recent database formats
Code written in Access 97 typically doesn't disambiguate these object references, since ADO wasn't introduced as a default library until Access 2000. Also, if one converts an Access 97 database to a more recent database format, a DAO reference will automatically be included in the converted database. However, if one simply creates a new database while using either Access 2000 or 2002, then imports all objects from an existing Access database, one will need to set a reference to the DAO 3.6 Object Library if any DAO objects are included in the code.
Issues with the Microsoft DAO 2.5/3.51
Compatibility Layer when converting Access 97 to more recent database formats
Access 97 databases converted from older versions of Access often contain the Microsoft DAO 2.5/3.51 Compatibility Layer Library reference, which allows the older DAO syntax to compile and run in Access 97. Newer versions of Access lack this library. If the older DAO syntax has not already been replaced by the time the previously converted database is converted to Access 2000 or newer versions, then the older DAO syntax must be converted to the current DAO syntax before the code will compile. After conversion to these newer database formats, remove the Microsoft DAO 2.5/3.51 Compatibility Layer Library reference and recompile the code. Correct any compile errors found by converting older DAO code to present coding standards. Continue compiling until there are no more compile errors. For more information, see the following KB article (applies to Access 2000/2002/2003):
Issues with the DAO library when
converting more recent database formats to the Access 97 format
Converting an Access 2000 or 2002 database to an Access 97 database on a PC that has multiple versions of Access installed (specifically Access 97 and any higher version) will cause a problem that won't be noticed until the application is opened on a PC that only has Access 97 installed. This PC will experience a MISSING reference error, because the converted database retains a reference to the DAO 3.6 Object Library, instead of the DAO 3.51 Object Library. This PC suffers the MISSING reference error because it doesn't have this higher version of the DAO Library, which shouldn't even be used with Access 97. The best solution is to open the database in Access 97 and reset the reference, by using the development machine. Then recompile the code and test the database before sending it to an Access 97 user. Alternatively, instruct the user to remove the MISSING reference and replace it with the DAO 3.51 Object Library reference to fix the problem and recompile the code.
Issues with converting more recent
database formats to earlier formats
Converting a database to work in multiple versions of Access, especially earlier versions, can fail -- even though the application works fine in the version of Access it was developed in:
"While it might sound odd, the conversion process merely converts the file format. It is not guaranteed to produce a working application in the older version, because you might have utilized methods and features that do not exist in the older version and which have no alternative method in that version.
To reliably produce files that work in
multiple versions, you need to develop in the older version and convert UP."
-- Rick Brandt, Microsoft Access MVP
Copyright
©
2004-2008 Tom Wickerath. All rights reserved.
Last Updated: Jan. 14, 2008
| About the author: Tom Wickerath is a chemist at The Boeing Company in Seattle, Washington, USA. He works in the Analytical Chemistry Group of Boeing's Materials & Process Technology (M&PT) organization. In the early 1990's, Tom became interested in the use of database technology to avoid many of the inefficiencies involved in using spreadsheets for the analysis of large amounts of data. Tom has taught Microsoft Access courses to college students for three years at Bellevue Community College in Bellevue, Washington. He has also been an active member of the Pacific Northwest Access Developer's Group (PNWADG) since the days of Access 2.0 and served as an officer of this group from 1998 to 2002. As a longtime Access expert, Tom has given presentations on complex Access subjects, including tools designed for Access developers, to the Seattle Access Group, the PNWADG and to Boeing Company personnel. Tom has provided his expertise to Microsoft Access developers and users for years in the Microsoft Access Newsgroups in UseNet, and he was awarded MVP for Microsoft Access in April, 2006. Check the Microsoft.Public.Access Newsgroup for Tom's latest expert advice and feel free to post any questions in these Newsgroups. Special Note From The Author: "If you find that any of these tips save you a lot of time and frustration with your database development, please consider making a tax deductible contribution to the Northwest Kidney Center, located in Seattle, Washington, USA. I've seen first hand how valuable their work is. With your help, they can do more research in the treatment and prevention of kidney disease, provide increased financial assistance to patients in need, and generally make kidney patients' lives more comfortable. You can even use PayPal to make a quick and easy donation online, and you'll feel better knowing that you have contributed to a better quality of life for those in need. Thanks." -- Tom Wickerath http://www.nwkidney.org/nkc/howYouCanHelp/donate/index.html For questions regarding this tutorial, please contact Tom at: |
Tom does not accept unsolicited requests for help. Contact Tom only if you have questions or feedback on this article.
| @ |
MEMBER OF PROJECT HONEY POT Spam Harvester Protection Network provided by Unspam |
