Solving Problems with Library References (e.g. Date() fails)

        27 votes: *****     28,526 views      1 comment
by Allen Browne, 20 April 2005    (for Access 97+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, June 2002.  Updated April 2010.


Solving Problems with Library References

Libraries are components that provide functionality. Access itself and its programming language (VBA) are two essential libraries in every project. If Access does not provide something you need (such as a calendar or tree-view), you may be able to find a library and add it. However, adding extra libraries increases the number of things that can go wrong.

What Problems?

Installing or uninstalling any software may overwrite, remove, or de-register libraries. Then simple functions like Date() or Trim() don't work.

To see what libraries an Access project has referenced, open any code window (e.g. press Ctrl+G), and choose References from the Tools menu.

If a library is marked MISSING, click the Browse button, and locate the correct file for the library. The table below lists the files for the crucial libraries of your version of Access.

If the library is not even shown, you may need to re-register it. Click the Windows Start button, and choose Run. Enter regsvr32 followed by the full path of the library file. Include quotes if the file name contains spaces, e.g.:
   regsvr32 "c:\program files\common files\microsoft shared\dao\dao360.dll"

Occasionally, the problem is not solved until you unregister the library and re-register it. Uncheck the missing library in Access. Close Access. Issue this command, and then the one above to re-register it:
   regsvr32 -u "c:\program files\common files\microsoft shared\dao\dao360.dll"
Re-register it (as above). Open Access, and select the library reference again.

Ambiguities arise if libraries have objects with the same name. For example, the DAO and ADO libraries both have a Recordset object, so Access 2000 and 2002 often choke when a DAO recordset was intended. Any of these ideas will solve the clash:

  • Remove the reference to either the ADO or the DAO library. (See below.)
  • Change the priority of the DAO library, by moving it above ADO.
  • Disambiguate each declaration, by specifying the library you intend, e.g.:
       Dim rs As DAO.Recordset

Inconsistencies and data corruption can result from referencing the wrong library for your version of Access. For example, the DAO 3.5 library was designed for Access 97, whereas DAO 3.6 is designed for Access 2000, 2002, and 2003. Access 2007 uses the ACEDAO library to give you the new functionality in the private JET engine. Use the table below to ensure you reference the correct files for your version of Access.

December 2006 update: There is a new issue with bad references when running multiple versions of Access on Windows Vista.

What libraries do I need?

Unnecessary libraries waste memory, slow loading, increase the chance of problems, and hamper debugging. The three essentials are VBA, Access, DAO. The VBA and Access libraries are built in, and will always have highest priority. Access will not let you remove these libraries from your database.

DAO stands for Data Access Objects. It is the object model written specifically for Access, so it's no surprise that it gives the best power and performance for data stored in Access tables.

ADO stands for ActiveX Data Objects. It is a more generic library, designed to handle data from sources other than Access tables (SQL Server) and interfaces other than Access (e.g. web-based.) If you are working on these enterprise databases, you don't need an explanation of ADO here.

Unfortunately, Microsoft made ADO the default library in Access 2000 and 2002. DAO is back by default in Access 2003 and 2007. Michael Kaplan illustrates why you need DAO in his blog posting, What does DAO have that ADO/ADOX/JRO do not?

Include other libraries when you have a reason to do so. For example, referencing the Microsoft Office 10.0 Object Library in Access 2002 gives you the FileDialog object so you can show the user a File Open dialog without resorting to API calls. (Note: FileDialog does not work in MDEs or the runtime version, and the msoFileDialogSaveAs option doesn't work at all.)

Which version?

This table lists the references appropriate to your version of Access, along with the file name if the reference is "missing":

Library name Access Ver. File type Library file (default location)
VBA
(Visual Basic For Applications)
2010   C:\Program Files\Common Files\Microsoft Shared\vba\vba7\vbe7.dll
2007 C:\Program Files\Common Files\Microsoft Shared\vba\vba6\vbe6.dll
2003
2002
2000
97 C:\Program Files\Common Files\Microsoft Shared\vba\vba332.dll
Access
(Microsoft Access xx.x Object Library)
2010 14.0   C:\Program Files\Microsoft Office\Office14\msacc.olb
2007 12.0 C:\Program Files\Microsoft Office\Office12\msacc.olb
2003 11.0 C:\Program Files\Microsoft Office\Office11\msacc.olb
2002 10.0 C:\Program Files\Microsoft Office\Office10\msacc.olb
2000 9.0 C:\Program Files\Microsoft Office\Office\msacc9.olb
97 8.0 C:\Program Files\Microsoft Office\Office\msacc8.olb
DAO
(Microsoft DAO 3.x Object Library
or
Microsoft Office xx.x Access database engine)
2010 14.0 ACCDB C:\Program Files\Common Files\Microsoft Shared\Office14\acedao.dll
MDB C:\program files\common files\microsoft shared\dao\dao360.dll
2007 12.0 ACCDB C:\Program Files\Microsoft Office\Office12\acedao.dll
MDB C:\program files\common files\microsoft shared\dao\dao360.dll
2003 3.6  
2002
2000
97 3.51 C:\program files\common files\microsoft shared\dao\dao350.dll
ADODB
(Microsoft ActiveX Data Objects 2.x Library)
all versions   C:\Program Files\Common Files\System\ADO\msado25.tlb

Where can I read more?

Doug Steele has many good links in his article, Access Reference Errors.

Tom Wickerath discusses ADO and DAO Library References in Access Databases.

For Microsoft's knowledgebase article on the libraries for Access 2002, see,
ACC2002: References That You Must Set When You Work with Microsoft Access


Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Solving Problems with Library References (e.g. Date() fails)'?


1.

Arulkumar says...

09 Aug 2010

 
very use full Thanks.....................

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).