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.
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 SubFind each of the files listed above and determine the version of each.
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 SubNext, on the workstation(s) where the application isn't working, check to see that the exact same version of each of those files is located in exactly the same location.
(Most of the material below is based on a posting from Frank Miller of Microsoft)
To view the current 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:
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.
There may be additional error messages that are caused by reference issues.
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:
The standard references for a Microsoft Access 97 database are:
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:
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:
Determine if the problem is with the database file or the target machine. There are two basic database file distribution methods:
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:
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.
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:
This can be done programmatically with the code provided in article Q194374 - ACC97: Error Message: Function Isn't Available in Expressions in Query Expression)
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:
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.
|ActiveX Control Name||Installed With||File Name|
|Animation Control||Office 97 ODE||COMCT232.OCX|
|Calendar Control||Access 97||MSCAL.OCX|
|Common Dialog Control||Office 97 ODE||COMDLG32.OCX|
|ImageList Control||Office 97 ODE||COMCTL32.OCX|
|ListView Control||Office 97 ODE||COMCTL32.OCX|
|Rich Textbox Control||Office 97 ODE||RICHTX32.OCX|
|MSInet Control||Office 97 ODE||MSINET.OCX|
|ProgressBar Control||Office 97 ODE||COMCTL32.OCX|
|Slider Control||Office 97 ODE||COMCTL32.OCX|
|StatusBar Control||Office 97 ODE||COMCTL32.OCX|
|TabStrip Control||Office 97 ODE||COMCTL32.OCX|
|Toolbar Control||Office 97 ODE||COMCTL32.OCX|
|TreeView Control||Office 97 ODE||COMCTL32.OCX|
|UpDown Control||Office 97 ODE||COMCT232.OCX|
|WinSock Control||Office 97 ODE||MSWINSCK.OCX|
|Web Browser Control||Office 97 ValuPack||SHDOCVW.DLL|
|ActiveX Control Name||Installed With||File Name|
|Animation Control||Office 2000 MOD||Comct232.ocx|
|Calendar Control||Access 2000||Mscal.ocx|
|Common Dialog Control||Office 2000 MOD||Comdlg32.ocx|
|Date/Time Picker||Office 2000 MOD||Mscomct2.ocx|
|Flat Scrollbar||Office 2000 MOD||Mscomct2.ocx|
|ImageComboBox Control||Office 2000 MOD||Mscomctl.ocx|
|ImageList Control||Office 2000 MOD||Comctl32.ocx|
|ListView Control||Office 2000 MOD||Mscomctl.ocx|
|Monthview Control||Office 2000 MOD||Mscomct2.ocx|
|Rich Textbox Control||Office 2000 MOD||Richtx32.ocx|
|Internet Transfer Control||Office 2000 MOD||Msinet.ocx|
|ProgressBar Control||Office 2000 MOD||Comctl32.ocx|
|Slider Control||Office 2000 MOD||Comctl32.ocx|
|StatusBar Control||Office 2000 MOD||Comctl32.ocx|
|TabStrip Control||Office 2000 MOD||Comctl32.ocx|
|Toolbar Control||Office 2000 MOD||Comctl32.ocx|
|TreeView Control||Office 2000 MOD||Comctl32.ocx|
|UpDown Control||Office 2000 MOD||Comct232.ocx|
|WinSock Control||Office 2000 MOD||Mswinsck.ocx|
|Web Browser Control||Office 2000 ValuPack||Shdocvw.DLL|
NOTE: In both cases, the UpDown ActiveX Control replaces the SpinButton Control from earlier versions.For additional information see
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:
For a list of currently available DLLs and OCXs, see Microsoft DLL Help Database
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.
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
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.Access Home
This page is maintained by
Last Updated: 25th June, 2009