Access Reference Problems

From time to time, you may find that common functions like CurrentDB, Str$ or Mid$ stop working. This condition is usually caused by a reference error in the database. (All Access databases contain references, which are a way of referring to another application's or project's type library. In general, you want to ensure that the files referenced in an Access application exist in exactly the same location on the client workstations as they did on the development workstation, and that each referenced file is the same version on all workstations.) Avoiding reference issues require an understanding of how libraries are referenced in an Access database and what is needed to install a database on a target machine without breaking these references.

Quick Solution

• Open any code module, then select Tools | References from the menu bar. Examine all of the selected references.
• If any of the selected references have "MISSING:" in front of them, unselect them, and back out of the dialog. If you really need the reference(s) you just unselected (you can tell by doing a Compile All Modules), go back in and reselect them.
• If none have "MISSING:", select an additional reference at random, back out of the dialog, then go back in and unselect the reference you just added. If that doesn't solve the problem, try to unselect as many of the selected references as you can (Access may not let you unselect them all), back out of the dialog, then go back in and reselect the references you just unselected. (NOTE: write down what the references are before you delete them, because they'll be in a different order when you go back in)
• Just so you know: the problem will occur even if the library that contains the specific function that's failing doesn't have a problem.

If that doesn't solve the problem (or if you want more information about what causes the problem), continue reading.

You should also read Michael Kaplan's (michka's) How to guarantee that references will work in your applications for how to ensure that your application won't have such problems.

Of course, recognize that if you're dealing with an MDE, you don't have the ability to change the references as outlined above. In that case, it becomes even more critical that the references be in the same locations and the exact same versions. Tony Toews has a free OCX/DLL Version Checker that should help you pinpoint any differences between the workstations.

Finally, Terry Kreft has written a References Wizard that's available at Dev Ashish's "The Access Web"

Unfortunately, it's not quite that simple if you're dealing with an MDE, or if you're using the Access runtime. This is because you cannot get to the References dialogue under those circumstances (nor can you change the References if you could).

What you can try and do is ensure that the same referenced files exist on all workstations. On the workstation where the application was developed, open up the MDB that was used to create the MDE. Run the following code to determine all of the required references:

Sub ListReferences()
Dim refCurr As Reference

  For Each refCurr In Application.References
    Debug.Print refCurr.Name & ": " & refCurr.FullPath
  Next

End Sub

In fact, if you grab the code Get Version of Office Exes (detecting Office patches) from "The Access Web", you can even have the code above determine the file versions for you:

Sub ListReferences()
Dim refCurr As Reference

  For Each refCurr In Application.References
    Debug.Print refCurr.Name & ": " & refCurr.FullPath & _
      " (" & fGetProductVersion(refCurr.FullPath) & ")"           
    Next

End Sub

(Most of the material below is based on a posting from Frank Miller of Microsoft)

Viewing Access Database References

To view the current references:

Access 97 (or earlier)

• open any code module, or open the Debug Window (press CTRL+G), provided that you haven't chosen the "Debug Window on Top" option
• on the Tools menu, click References.

Access 2000

• start the Visual Basic Editor (press ALT+F11)
• on the Tools menu, click References.

How Access Resolves Visual Basic for Applications References

The pertinent file, for example, a type library, object library, or control library, for each reference is loaded according to the information displayed in the References box. However, if the file is not found, Access takes the following steps to determine the location of the file.

For each reference that is selected, Access does the following:

1. Access checks to see if the referenced file name is currently loaded in memory.
2. If not, Access verifies whether the RefLibPaths registry key exists. If so, Access looks for a named value with the same name as the reference. If there is a match, Access loads the reference from the path that is pointed to by the named value.
3. Access next searches the following locations for the referenced file:
   1. Application Folder (location of Msaccess.exe)
   2. Current Folder that you see if you click Open on the File menu
   3. WinDir where the operating system files are running
   4. System Folder located in the WinDir
   5. the PATH environment list of folders directly accessible by the system.
4. If the file cannot be found, then a referencing error occurs.

• Q280465 - ACC97: How Access 97 Resolves Visual Basic for Applications References
• Q248941 - ACC2000: How Access 2000 Resolves Visual Basic for Applications References
• Q286300 - ACC2002: How Access 2002 Resolves Visual Basic for Applications References

Reference Error Messages

There are a number of symptoms that can relate to a missing file or a file that has a different version than the one used in the database. In most cases, the correct steps to resolve the issue are outlines in the articles found by searching the knowledge Base for the specific error message. In some cases, a dependency file is not properly matched with the primary file.

1. An error message in the format of Method <method name> of Object <object name> Failed. This typically is caused by a problem with a programming type library such as an invalid DAO DLL file. There are a number of articles on the various forms of this message. Go to MSDN Online Search and do a look-up on the specific message you're getting.
2. Error message Function is not available in <usage> expression. This can be caused by a problem with a programming type library or if the code doesn't specifically call out the proper library and the file is listed at a lower priority in the references list than a file that contains the same function name. For example, using DAO code with the ADO library listed at a higher priority than the DAO library. Another common cause is an OCX that has be inserted in a form or report. See article Q194374 - ACC97: Error Message: Function Isn't Available in Expressions in Query Expression.
3. Error Message Can't find project or library. This message can occur any time the file can not be located. This often occurs when the file in the references list cannot be found and is flagged as MISSING. Often, the file exists on the development machine but not on the target machine. See articles Q160870 - ACC: VBA Functions Break in Database with Missing References, Q208218 - ACC2000: VBA Functions Break in Database with Missing References or Q283806 - ACC2002: VBA Functions Break in a Database with Missing References.
4. Error messages Variable not defined and User-defined type not defined. Occurs when you use the User-Level Security Wizard to secure a database that references libraries other than those included by default. References to libraries that existed in the unsecured database are not automatically created in the new secure database. See article Q174342 - ACC: User-Level Security Wizard Doesn't Add Library References.
5. Error messages Run-time error 5, Invalid procedure call or argument, The library which contains this symbol is not referenced by the current project, or The library which contains this symbol is not referenced by the current project, so the symbol is undefined. This is caused by a reference to a database, a type library, or an object library that is marked as MISSING. See article Q231413 - ACC2000: Error Messages That Indicate a Missing Reference.
6. Error message ActiveX component can't create object. This error message does not necessarily imply that an ActiveX control is involved. For example, in one possible cause, DAO, an ActiveX component, cannot create an object because the DAO Automation Server cannot be started. Frequently, the cause is unregistered or improperly registered DLLs that provided referenced functionality for the application. See article Q244264 - INFO: Troubleshooting Error 429 When Automating Office Applications. Although this article is written from a Visual Basic viewpoint, it covers may of the causes for this error message.

There may be additional error messages that are caused by reference issues.

Reference Issues on the Development Computer

Creating a new blank database and importing objects from another database file can create references issues when the code or OCXs rely on different references than those that are included in a default database. The standard references for a Microsoft Access 2000 database are:

• Visual Basic for Applications
• Microsoft Access 9.0 Object Library
• OLE Automation
• Microsoft ActiveX Data Objects 2.1 Library

The standard references for a Microsoft Access 97 database are:

• Visual Basic for Applications
• Microsoft Access 8.0 Object Library
• Microsoft DAO 3.5 Object Library

If the source is another Access 2000 database, verify that the references match. If the source is a prior version of Access, DAO 3.5 or earlier is likely in use but not provided by Access 2000 by default. Try removing the reference to Microsoft ActiveX Data Objects 2.1 Library (if it exists) and add the following:

• Microsoft DAO 3.6 Object Library

In some cases, VBA code can be satisfied by more than one library. Unless specified, the highest priority library in the references list will be used. For example, if you have Access 97 database controls that are imported into an Access 2000 database, any DAO code will be executed from the ActiveX Data Objects (ADO) library if this library is higher in priority than the DAO 3.6 library or the DAO 3.6 library is not in the list. This can be avoided by specifically referencing the desired library as follows:

• Dim db as Database - No specific reference, picks highest priority library
• Dim rs as DAO.RecordSet - Reference to DAO only, fails to compile if DAO is not referenced
• Dim fd as ADODB.Field - Reference to ADO only, fails to compile if ADO is not referenced

Distributing Database Files

Determine if the problem is with the database file or the target machine. There are two basic database file distribution methods:

• simply copy the file from the development computer to the target computer
• use a Setup or Package and Deployment Wizard to create a setup package

With the first method, only the database file is copied so you must manually insure that all files listed in the reference list are available, at the correct version level, and in the same relative location on the target machine as they are in the development machine.

For the second distribution method there is an excellent article that you can use as a checklist when developing RunTime applications in Access: Q180284 - ODE: Avoiding Common Mistakes with Distributable Run-time Apps

Another excellent article that lists most of the issues that developers need to be aware of is Q154977 - Office 97 Developer Edition Articles Available by E-Mail, but unfortunately, Microsoft seems to have removed it from their site!

When developing Run-Time applications in Access, it is important to realize that some operating system files need to be distributed with the Run-Time application. The packaging of these files is performed automatically by the Access 97 ODE Tools Setup Wizard or Access 2000 Microsoft Office Developer (MOD) Package and Deployment Wizard (PDW). The versions of the files that are included can sometimes depend on other applications installed on the development computer which have modified the operating system files.

To insure that the correct versions of all files will not conflict with any files on the target computer when the Run-Time application is installed, developers usually follow these guidelines:

1. Develop the Access database on any computer.
2. Create a computer environment that has had its hard drive reformatted and only the oldest applicable operating system, Office, and ODE/MOD installed and run the ODE Tools Setup Wizard/PDW on this computer to create the Run-Time version of the application. This will insure that the revision levels of the files will work in any of the target machines.
3. Create a computer environment that has had its hard drive reformatted and ONLY the operating system is installed and test the Run-Time application in this environment. If it fails, we know there is something wrong with the Run-Time application that must be addressed before it is distributed.

If the Run-Time application works successfully in the operating system test machine and does not work on the target machine, then we know that the target machine, and not the application is at fault and there is likely an incompatibility or corrupted file on the target machine that needs to be identified and corrected.

Refreshing the Reference List

When the problem involved an OCX (ActiveX Control), sometimes the issue can be resolved by simply refreshing the reference list. This can be done by:

1. In the References dialog box, pick any single reference that is not already selected, click to select it, note which one you selected, and then click OK.
2. On the Tools menu, click References again.
3. Click to clear the reference that you selected in step 1, and click OK.

This can be done programmatically with the code provided in article Q194374 - ACC97: Error Message: Function Isn't Available in Expressions in Query Expression)

Distributing Database Files with OCX Controls

There are two types of licenses for OCX controls: a design-time and a run-time license.

A design-time license that allows for the insertion of licensed OCX controls from the Office Development application into forms and reports in an Access database. A run-time license, on the other hand, allows use of the control in an Access database on a machine that does not have the Office Development application package installed but does not allow the insertion of new licensed OCX controls. To install the runtime license, you will need to distribute the OCX using the Setup Wizard that ships with the Office development application to write the license for the control in the target machine's registry.

The Missing indicator seen when a Module is open in design view and Tools menu, References is selected, is caused by the reference to the Common Dialog control on the target machine does not match the source as stored in the database file from the original machine.

In addition, many applications use the Common Dialog control with a run-time only license. Only the distributable version of the control, such as the one provided by the Office 97 Developer's Edition (ODE), has the required license to be used in a distributed application. This version of the Common Dialog control is meant to be installed as part of a Run-Time application built with the Setup Wizard in ODE.

When we distribute a database file without installing the distributable Common Dialog control, the results can vary from the control's reference being "Missing", to getting a You don't have the license required to use this ActiveX control error message when the non-distributable control is already installed on the target machine.

Even when the database file is part of a Run-Time application, it is possible to get the You don't have the license required to use this ActiveX control error message when the non-distributable control that is already installed on the target machine is of a higher version than the one provided by your Run-Time application. This can occur because the setup program does not overwrite newer versions of files with older versions of the same file.

For additional information on this issue and several methods that can be used to resolve this issue, see the article Q172859 - ODE97: "You Don't Have a License" Error Using ActiveX Control

For additional information on this topic, see the following articles:

• Q208283 - ACC2000: Supported ActiveX Controls for Microsoft Access 2000
• Q202104 - ACC2000: Custom ActiveX Control Features Supported in Access

OCX Controls Supported in Access

The following tables lists the ActiveX controls that have been tested and verified for use with Microsoft Access 97 and Microsoft Access 2000. The ActiveX controls listed below are the ONLY ActiveX controls supported for use with Microsoft Access 97 or Microsoft Access 2000.

When you open a form or report in Design view, you may see more controls than those listed in this article when you click ActiveX Control on the Insert menu, or when you click More Controls on the Toolbox toolbar. However, those controls may not work correctly in Microsoft Access 2000.

You may also notice that both Microsoft Office 97 and Microsoft Office 2000 install several Microsoft Forms 2.0 controls, which are listed in the Insert ActiveX Control dialog box. Although these ActiveX controls work in Microsoft Access, the native Microsoft Access form and report controls provide more functionality and they function more quickly than their ActiveX equivalents.

NOTE: In both cases, the UpDown ActiveX Control replaces the SpinButton Control from earlier versions.

• Q160126 - ACC97: Supported ActiveX Controls for Microsoft Access 97
• Q208283 - ACC2000: Supported ActiveX Controls for Microsoft Access 2000

The Common Dialog Control OCX

The Common Dialog Control (Comdlg32.ocx) is a popular OCX used to display the Open dialog box. This control is distributed with many Access applications and can cause references issues.

For example, when an Access application that contains the Common Dialog Control is installed, if there is a newer version of this control installed by another application, the reference to the control is broken and you may get a Function is not available error. The only solution is to recreate and redistribute the application with the updated version of the control.

The same thing can occur when the machine upon which the Access application is successfully installed. Subsequently installing another application with a newer version of the control causes the previously installed application to fail as previously mentioned. The resolution is the same as above.

Although this scenario can occur with any OCX, the popularity of this control makes this issue more visible.

The recommended solution for the Common Dialog OCX is to use an API call in code to provide the same result without using the OCX. This issue is described and the recommend sample code is available in the following articles. Note that although the code sample is provided as a solution for Visual Basic for Windows, the sample code will work in Access as well:

• Q237359 - MOD2000: COM Add-ins Using ActiveX Controls Cause Errors
• Q161286 - HOWTO: Use a Common Dialog File Open Dialog with Win32 API

For a list of currently available DLLs and OCXs, see Microsoft DLL Help Database

Reregistering a File

A file may be in the references list, but the file itself may not be referenced correctly with the system registry. If this is suspected then the following steps can be taken to register the file.

1. Click Start, Find, File or Folders....
2. In the Named box, type: REGSVR32.EXE
3. Be sure the Look In box points to the root of your hard drive. (Usually drive C).
4. Be sure the Include Subfolders checkbox is selected.
5. Click Find Now.
6. Once found, click Start, Run, and delete anything in the Open box.
7. Drag the REGSVR32.EXE file from the Find dialog and drop it into the Open box.
8. Repeat steps 2 - 7, this time searching for <FileName.dll>
9. Once this DLL has been dropped into the Open box along with REGSVR32.EXE, click OK.
10. Test to see if the problem continues in Microsoft Access.

If you do not find REGSVR32.EXE on your machine, check other available machines for this file. Otherwise, you can obtain this file from article Q161983 - ACC: Regsvr32a.exe Available

One Final Note

Remember to compile all modules after adjusting references. With the module still open, click Debug, then click Compile <database>. If the modules do not compile there may be additional unresolved references.

This page is maintained by Last Updated: 25th June, 2009