In the March 1999 issue’s “Access World News,” we reported on how Access references can break when end users update their systems or install new software. Hardin explains the problem that causes functions like Left() and Date() to fail, delves into some mysteries in Access’s use of references, and presents a module that can get your applications back on track.

The first time it happened to me, my stomach bounced against the soles of my shoes. I’d just finished an Access application for a new client, tested it thoroughly, and delivered it to the client’s office. As soon as I started to demonstrate the program, my code failed on a simple Left(). The error message insisted that the function wasn’t available. Luckily, I had an .MDE version of the project with me and it ran just fine, so I was able to implement the application. My client insisted on available source code, though, and I didn’t know how to make the MDB version work.

One of my partners had just hit the same snag in a different program and had discovered a cure: Open a code module and open the Tools | References menu. On the References list, he found a reference to an ActiveX control that was flagged as an error. When he fixed the reference, everything started to work again. In my case, the ActiveX control that was causing my program to fail was the standard Common Dialog control. But what did this have to do with Left()? And why had Access broken?

Part of the answer can be found Microsoft Knowledge Base article Q194374 (http://support.microsoft.com/support/kb/articles/Q194/3/74.asp). In essence, this article says that if you develop an application with Access 97 or Office Developers Edition 97, install your application on an end user’s computer, and then that user installs any program written with newer development tools, your Access program might suddenly fail.

The problem, according to the Knowledge Base article, is that newer programs can install newer versions of ActiveX controls (like the Common Dialog control). When these newer controls are installed on a computer, they’re “registered differently” (in the words of the article), and Access can’t use them until “the new Registry information is refreshed in the References collection.”

That explanation makes it sound like the control writer (in this case, a different department at Microsoft) made a mistake in updating the control and that Access and your application are innocent victims. If that were true, programs written with other tools, such as Visual Basic and Visual C++, would suffer the same fate. Well, they don’t. So, as an Access developer, I had to take a deeper look at Access to understand why it sometimes suffers these nervous breakdowns and what I could do about it.

Mishandled references

References are at the heart of Microsoft’s ActiveX/COM strategy. When you use an ActiveX component, whether it’s the Common Dialog control or Microsoft Word, Access stores information about the component in your application’s References collection. A reference points to a type library that describes, in machine-readable format, the objects that the ActiveX component makes available to programs that use it. The type library may be in the .DLL, .EXE, or .OCX file that makes up the component; it may also be in a separate .TLB or .OLB type library file.

In order to run properly, Access always has two items checked off on its References list: The first is to Access’s own type library, MSACC8.OLB, and the second is to the VBA type library (VBA332.DLL). By default, in every new database, Access adds a reference to DAO350.DLL, which is needed to use the Jet database engine that provides the DAO object model.

If you add an ActiveX control, such as a Common Dialog or TreeView control, to an Access form, Access automatically adds an appropriate entry to your application’s References list. You can also add references manually in order to get Intellisense support for objects that you’re using in your application. The References dialog box is available from the Tools menu, but only when a code module is open in the Access code editor and has the focus. (You can never open the References dialog box in an .MDE application, because Access won’t let you open a code module.) You can also view your references using the Object Browser (see the sidebar “The Object Browser”).

The problem that I was having was due to an Access idiosyncrasy. A reference is said to be broken when Access can’t find the file that the reference points to or if the file doesn’t match what Access expected to find. If, for example, your application has a reference to the Windows Common Controls library, which is contained in COMCTL32.OCX, and if that file is updated to a new version, Access thinks the reference is broken. Other applications get along just fine, relying on the ActiveX “contract,” which says that existing interfaces (essentially, calling methods) won’t be broken in updated type libraries. Access is far too suspicious to rely on that contract.

At times, when Access thinks one reference is broken, it will act as if virtually all of its references were broken. Since the References list includes references to both Access and VBA, chaos results. Code that used to work doesn’t work at all anymore. Queries fail, forms and reports lose their data, and Access seems to suffer a nervous breakdown. You might get mouse errors on ActiveX controls that don’t even reference the mouse. Almost anything can and does happen, but the result is that your application won’t run properly. If you have any VBA function calls in your modules, forms, or reports, including in the control source or record source of any control on a form or report, Access will raise an untrappable error and tell you that it can’t do anything about it.

When are common ActiveX files updated? Whenever the user installs software that was created with a compiler more recent than the one used to create Access itself. These include, but aren’t limited to, programs created with:

• Visual Studio 5, Service Pack 3 (including Visual Basic and Visual C++) or later
• any tool from Visual Studio 6
• a new version of Internet Explorer
• a Windows service pack

The same also occurs if you develop on Windows 95/98 and then move your application to a Windows NT computer, or vice versa.

Fixing references manually

The manual fix for this problem is usually very simple. Open a code module, open the Tools | References dialog box, and look for a reference that starts with “MISSING:”. If you find one, you’ve identified the problem as references-related. Now you need to make Access update the references for your project. An easy way is to select a new item and check it as referenced. Close the References dialog box, reopen it, uncheck the new item, and close the dialog box again. Then, from the Debug menu, select Compile and Save All Modules.

The other tack you can take is to remove the reference to the offending file. First, carefully record the file that the reference refers to–assuming that you can read the filename as displayed in the dialog box. This is one of the few Microsoft dialog boxes that truncates long path and filenames at the end, often obliterating the actual filename and extension. After unchecking the MISSING: reference, close the References dialog box. Reopen it and use the Browse button to find the file and set a reference to it. Then close the dialog box again and recompile your project.

You’d think that Access could do all of this itself if it found a missing reference. But it doesn’t. You could, of course, give your end user the preceding instructions over the phone, but only if:

• your application isn’t in MDE format on the end user’s computer (luckily, MDE applications break a lot less often than MDA ones).
• your application is unsecured.
• your application is secured but the end user has the necessary security privileges to open a code module and recompile.
• you’re willing to give the user your security password to get into the program.

The real solution is to add code to every application that checks references when the application starts and fixes them when necessary. The Knowledge Base article that I mentioned at the beginning of this article has code that’s supposed to do just that–however, it makes several assumptions that might not be valid, and it doesn’t always work.

I can’t really blame Microsoft–such code is extremely difficult to develop and test. There are three reasons that reference-fixing code is difficult to write:

1. It treads on only partially documented waters.
2. Access’s References collection and Reference object (which are new with Access 97) don’t always work the way they’re supposed to.
3. All VBA functions and constants (everything visible in the Object Browser when you select the VBA library) is off-limits to your code because VBA will likely not work when a reference is broken.

Finding problems

Access stores individual references in a simple collection that has two public properties, four public methods, and is capable of raising two events. In addition, the collection has a hidden method, _NewEnum, which lets your code iterate through it using the For…Each structure. The official list of properties, methods, and events for both the References collection and the Reference object is shown in the sidebar “The References Collection and the Reference Object.”

I’ve only found four of the eight exposed items of the References collection useful. In fact, I’ve found that the AddFromGuid method is almost useless because it has a tendency to crash Access. So that leaves one property and three methods that appear to be safe and useful.

Each member of the References collection is a Reference object, which has nine properties, no methods, and no events. The property called Collection is generally useless; it returns a pointer to the References collection that you used to get to the reference itself. The other eight properties are potentially useful.

Looking at the references and reference documentation, you might guess that finding and fixing a broken reference would be a matter of writing code like this:

Dim ref as Reference

For each ref in Application.References

If ref.IsBroken = True Then

(fix it)

End If

Next ref

That would be a reasonable piece of code except for two problems. The first (and minor) problem is that VBA is often unusable if a reference is broken, and both “True” and “False” are constants defined in VBA. If your code needs True/False tests while it’s checking for broken references, it should use -1 and 0 instead of these constants.

A much more serious problem is that the reference’s IsBroken property is, well, broken. It will only rarely signal a reference that must be fixed before your application will run correctly. After a lot of testing with both Project and TypeLib references, I’ve found that there are four tests that, taken together, are reliable in finding a broken reference:

1. A query that contains a VBA function (such as Left() or Format()) fails.
2. The project, which was once compiled, is no longer compiled.
3. A Reference object’s FullPath property returns a Null or an empty string.
4. A Reference object’s Name property returns a Null or an empty string.

The first test uses a query that contains VBA code. If you try just running VBA code when some references are broken, your application will halt with an untrappable error. However, if VBA isn’t working, a query that contains VBA code will fail with error 3075: “Function isn’t available in expressions in query expression…” Your program can trap error 3075 and use it as a trigger to fix broken references. You could add a query to your project to perform this test. I prefer to create the query from code to keep extra queries from littering the query window. I use code like this to perform the test:

Dim rst as Recordset

Dim strSQL as String

Dim fFixRefs as Boolean

strSQL = _

"SELECT Left$("AB",1) As Test " & _

"from tblCompanies"

On Error Resume Next

Set rst = CurrentDB.OpenRecordset(strSQL)

If Err.Number = 3075 Then fFixRefs = -1

On Error Goto 0

rst.Close

Set rst = Nothing

To find out whether the current project is compiled, your code can test use this test:

If Application.IsCompiled = 0 Then ...

The third and fourth tests require iterating through the References collection and looking for a reference that has IsBroken set, has a blank FullName, or has a blank Name property:

Dim ref as Reference

For each ref in References

If (ref.IsBroken = -1) Or _

(ref.FullPath & "" = "") Or _

(ref.Name & "" = "") Then

fFixRefs = -1

End If

Next ref

Fixing references with code

If one of the tests shows that one or more references need fixing, the next question is how to do it. The Knowledge Base article takes an easy approach: It finds the first reference that isn’t built in, saves its file name (the FullPath property), removes it from the References collection, and then adds it back to the collection using the AddFromFile method. The idea is that any change in the References collection will cause Access to refresh the collection, upgrade all type library references to their latest version, and get the application back on track.

That approach works only when the application has been running successfully on a machine and then breaks because a type library has been updated when another application was installed. It won’t work in the case I originally faced–moving an application from Windows 95 to Windows NT or vice versa–because the type libraries are stored in different locations (usually \Windows\System in Windows 95 and \Windows\System32 in Windows NT). Using AddFromFile with the Windows 95 filename will only cause an error. Microsoft’s solution won’t work if one computer or the other uses a non-standard name for the Windows directory (for example, \Win95 instead of \Windows) or in a number of other situations.

What you need is a routine that will handle all of the potential problems (or at least report those it couldn’t fix), not just the specific glitch that occurs when the user updates another program.

Type libraries are easy to find through the system Registry. They’re stored under the HKEY_CLASSES_ROOT\TypeLib heading as a series of GUIDs. A GUID is the ugly 128-bit Globally Unique ID Number that uniquely identifies everything in the COM world. Beneath each TypeLib GUID in the Registry is a list of subkeys that shows all of the versions of that library that have been installed on the computer. Subkeys beneath each version number lead to the full path and name of the required file (see Figure 1). So all the program has to do is find the GUID for the current reference (easily done through the object’s GUID property), open the system Registry, navigate to the GUID listing, find the highest version number, and look up the filename.

To write a program that would fix broken or missing references to type libraries, I used two references of my own: an API reference and the Access 97 Developer’s Handbook (which described a number of routines in MSACCESS.EXE that let me make API calls without needing VBA functions).

Once I could access the Registry, my next problem was finding the highest version number for any type library without using any VBA functions to manipulate the string version numbers. My solution was to iterate through all of the version number entries, copying the version number string into a real number variable, and then manipulate the numeric variable by assigning it to an integer variable to force rounding. My code will work correctly for any major version and for any minor version up to .9. I did a search of the Registry and found no minor version numbers above .5. So, at least for a now, the code should work correctly.

The final step is to pull the filename from the Registry. That almost stumped me. The filename is returned, even by the routine in MSACCESS.EXE, in a null-terminated string. The problem was how to truncate the string and remove the trailing null character without any VBA. My final solution, which I admit is difficult to follow, does the following:

1. Retrieves the filename (using the MSAccess Registry functions) as an array of bytes.
2. Loops through the array to find the first value of 0. That’s the first byte to throw away.
3. Dimensions a second byte array to twice the number of bytes that you want to keep and copies the filename to it, filling every other position with 0 to simulate a Unicode string.
4. Assigns this “Unicode” array to a string variable, which, in Access’s version of Basic, converts it back to an ANSI string.

The code isn’t pretty and it isn’t efficient, but it works and it doesn’t use any VBA functions. If I’m missing something obvious (and I feel like I am), please send me a better solution.

After fixing

After my code has fixed any broken references, it needs to recompile the application. The Knowledge Base article includes a “hidden” SysCmd function that your program can use:

Call SysCmd(504, 16483)

This SysCmd call has several advantages over the following documented approach:

DoCmd.RunCommand acCmdCompileAndSaveAllModules

To use the RunCommand method, you must have a module open in design view. Also, if RunCommand fails, it will open the first module it finds that doesn’t compile, put the cursor on an offending line, and display an error message. This is something that will confuse and frighten many end users.

The hidden SysCmd function, however, never raises an error and doesn’t require that any modules be open in design view. It can even be used in an MDE project without causing an error. The best way to find out whether it has succeeded (since it doesn’t raise an error) is to test Application.IsCompiled after it has run.

Troubles in paradise

My final solution is available in the accompanying Download file. It includes a module that can be added to any application and a simple form that tests and runs various parts of the code. The form’s code also collects current reference information and saves it in a table for later inspection. The main function, UpdateReferences, is called by an AutoExec Macro so that it will run before any forms are loaded, even if one is specified in a project’s Startup properties. I’ve used this code in several applications, and it seems to work . . . most of the time.

The code succeeds if you update a referenced type library or move your application to a computer that has a more recent type library than the one you used on your development machine. It also succeeds in pinpointing and reporting references that it can’t fix. However, it appears to suffer from two problems.

First, the compilation performed by the SysCmd call, while it works when it’s called, sometimes fails to “stick.” By that, I mean that the application will sometimes uncompile itself after it’s been compiled in this way. If that happens, the next time the user runs the application, there will be another pause while the program again fixes references and recompiles itself. Users might be annoyed waiting for a compilation each time they start the application, but the application does run smoothly. The only solution I’ve found is to manually open a module and choose Debug | Compile and Save All Modules. If the application still doesn’t compile, close all modules, repair the database, and then, once again, try to compile and save all modules.

The second problem is much worse, and I have no solution for it. If you create or compile a project on a computer that has a later version of a type library, and then move it to a machine with an earlier version, the sample code will fail. Often, it won’t even run. In such cases, you have at least two choices. First, you can copy your newer type library to the client’s second machine, register it with REGSRV32.EXE, and then start the Access application again. However, if the client’s machine is used for development, the user might suddenly find that he or she “does not have a license to use . . .” whatever type library you’ve replaced.

The second solution, if you’re sure that your program doesn’t rely on any new features of the type library, is messy and sometimes must be repeated once or twice before it succeeds. First, open a new database and import everything from the old one to the new one. Second, open any code modules and manually set the necessary references. Third, close all modules, forms, and so forth, and repair and then compact the database. Fourth, open a module and compile and save all modules.

Avoiding all problems

Perhaps the best way to avoid all of these problems is never to use any extra references at all, or at least use as few as possible. That means, among other things, that before you distribute any application, you should check the References dialog box for any references that are checked off but that you’re not using. If you’ve dropped a Common Dialog control on a form, for example, and then changed your mind and deleted it, your application will still have a reference to COMCTL32.OCX. If the end user updates that file, your application will break, even if it doesn’t use the control.

If you avoid all ActiveX controls, .DLLs, and .EXEs in your program, it will probably never break because a user has installed or updated another program. On the other hand, your application might be the poorer for not using a TreeView, ListView, or other control that would make the user interface more intuitive. You’ll have to weigh the advantages and disadvantages, especially for applications that will be distributed to a wide audience.

Sidebar: The Object Browser

In addition to viewing your references through the References list, you can also browse them through the Object Browser (see Figure 1a), which is available from the View menu when a code module is open in the editor. The top combo box in the Object Browser lists all of the loaded type libraries–every component to which Access has a reference. When you select a library, its exposed functions, objects, properties, and methods are shown in the lists that display in the bottom two panes of the browser. The Object Browser is handy for viewing the definition of a function call, the return type of a property, and even for finding Subs and Functions in your application that are hidden in some obscure module.

Sidebar: The References Collection and the Reference Object

These are the documented properties, methods, and events of these two parts of the Access Application object:

References collection

Reference object

Other Topics

Read this to feel PtrSafe about Office 64 Bit compatibility