This month, Doug Steele explains why Access applications suddenly stop working, or work on some machines but not others.

My database was working perfectly for the past few months, but suddenly I’m getting an error message: “Function isn’t available in expressions in query expression.” What happened?

Another common variation of this question is “My database works fine on my machine, but when I give a copy of it to a co-worker, all sorts of errors arise.” In both cases, a very likely cause for the problems is with the References that Access is using.

Like most other Windows applications, Access allows you to reference other files—type libraries (.TLB), ActiveX controls (.OCX), dynamic link libraries (.DLL), and so on—so that you can use the objects supplied by that file in your code. This allows you to reuse code efficiently, but an unfortunate side effect is that there’s just one more thing that can go wrong with your application!

Many of the functions that you think of as being part of Access are actually supplied by some other means, the most common one being the VBA library. For instance, if you’ve ever used the function Date(), you’ve actually been calling a routine built into the _DateTime library that comes with VBA. Each Access database stores the details of any files that it uses. If you have a code module open, you can select Tools | References from the menu bar and see what references your application currently has (as shown in Figure 1).

Figure 1

Sometimes, though, there can be problems with one or more of the references. This can be because the file(s) you reference on your development workstation may not exist in the same location on other workstations, or the version(s) that exists on the other workstations may be different from the versions of the file on your workstation. And while I mentioned other workstations in the preceding sentence, it’s entirely possible that you could break the references on your own development workstation. Installing something new on your workstation is a typical cause of broken references.

When that happens, you’ll see something like the message in Figure 2 in your References list.

Figure 2

Note the label “MISSING:” to the left of the reference to Word 9.0. In this case, I know that it was because the machine on which I checked the references doesn’t have Word 9.0 installed on it.

When you use a function, VBA has to locate that function in order to know what to do with it. The first thing it does is search through the current Access module, then through all the other modules in the project, and finally through the referenced files (in the order in which they’re listed in the References window). If Access encounters a problem while it’s doing this search, the function call will likely fail, even if the library containing the function doesn’t have a problem itself.

If a specific file isn’t found where the Reference object says it should be, Access will even look for it in some other likely places. (The exact details of where it looks vary a bit from version to version. If you’re interested, you can check out  http://support.microsoft.com/kb/209849/en-us for Access 2000.) While it might seem as though Access should be able to resolve issues like this, sometimes it just isn’t possible.

Unfortunately, to do justice to the whole area of References would require more space than they give me for this column. As a result, I’m going to skip over most of the details, and hope that you’ll just accept what I’m saying. In addition to the preceding Microsoft references, Peter Walker has some excellent information on his site. Take a look at www.papwalker.com/ref101/index.html. Realistically, you can’t prevent Reference problems. However, you can detect them and (hopefully) correct them before they cause trouble.

What can I do in my database to reduce problems with References?

The first thing is to not include any references that aren’t necessary for your application. In order for your application to work, there are certain references that must be present. As a bare minimum, you must have references set to VBA and to Access itself. To do anything useful, you also need a reference to either DAO or ADO (or both).

Table 1, copied from Allen Browne’s http://allenbrowne.com/ser-38.html  page, summarizes the base references along with the file that each refers to.

Table 1. Base references and the files they refer to.

For the record, the references in Table 1 are seldom the problem. It’s usually only if you’ve added references to other libraries, or use ActiveX controls, that you may run into problems. Make sure that you know what every referenced library is for! (I generally give the advice “Starve your references.”)

How can you tell whether or not a reference is truly necessary? Try removing it (remembering to keep track of what it was so you can add it back if you need to!), and then compile all of the modules in the application. If the application compiles cleanly, you probably didn’t need the reference. How you compile all the modules is a little different between versions of Access, although it’s always under the Debug menu (see Figure 3 and Figure 4 for examples).

Figure 3                                                 Figure 4

One very common cause of problems with References is the Common Dialog control (comdlg32.ocx), which many people use to provide the standard Windows File Open/File Save dialog. I don’t know why, but this control is very prone to versioning problems. Instead, consider calling the API directly. There’s sample code to demonstrate how to do this, courtesy of Ken Getz, at www.mvps.org/access/api/api0001.htm at “The Access Web.”

Another common offender is the Calendar control (mscal.ocx, or variations thereof). Again, you can make API calls instead: Stephen Lebans has a good example showing how at www.lebans.com/monthcalendar.htm. Alternatively, Tony Toews has other possibilities listed at www.granite.ab.ca/access/calendars.htm.

Something else that’s a good idea is to consider using late binding, rather than early binding. (For a discussion of the two, check out Excel late binding http://support.microsoft.com/kb/219151/ on the Microsoft site, or see what Tony Toews has to say at www.granite.ab.ca/access/latebinding.htm.) I realize this goes against the normal advice, but there is method to my madness.

When you use early binding, you do gain a lot. Early binding gives you syntax checking during compilation rather than at runtime, IntelliSense information when you’re working with the objects, support for built-in constants, and better performance (since the entry points for the Automation server are already known, and the data types and syntax have already been verified). However, it does require you to set a reference.

The error message in Figure 2 is the result of early binding. In that scenario, I did my development on a workstation that had Word 2000 installed on it. When I set the reference to Word 2000, everything worked fine on my workstation. When I deployed to a workstation without Word 2000 installed, not only did the Word-related stuff not work, but other functions stopped working as well. What could I have done instead?

For the sake of argument, let’s assume I had the reference to Word so that I could use its spell-checking capabilities, and that my code looked something like this:

Function TestSpelling(WordToCheck As String) As Boolean

Dim objWord As Word.Application

Set objWord = New Word.Application

TestSpelling = objWord.CheckSpelling(WordToCheck)

objWord.Quit

Set objWord = Nothing

End Function

To make that code work without requiring a reference to Word, I declare objWord as an object and instantiate the object using CreateObject, rather than the New keyword:

Function TestSpelling(WordToCheck As String) As Boolean

Dim objWord As Object

Set objWord = CreateObject("Word.Application")

TestSpelling = objWord.CheckSpelling(WordToCheck)

objWord.Quit

Set objWord = Nothing

End Function

You don’t have to declare a variable as Object to use CreateObject—I could have used CreateObject in my early binding code also.

With late binding, if Word doesn’t exist on the user’s workstation, you’ll get a trappable error (“Error 429: ActiveX component can’t create object”). With early binding, if Word doesn’t exist on the user’s workstation, you’ll get a runtime error (“User-defined type not defined”).

To minimize the inconveniences of late binding, one common technique is to use early binding while you’re developing the application. Once you’ve got everything working, all you need to do is remove the reference, and change your instantiation to use CreateObject as before. You’ll get all the IntelliSense support that you need while writing code without the problems with the References list at runtime.

The major disadvantage of this technique is if you use intrinsic constants associated with the object. For instance, if you had a reference set to Excel and you wanted to run the Auto_Open macro associated with a workbook, you might have written code like this:

Dim objXL As Excel.Application

Set objXL = CreateObject("Excel.Application")

With objXL.Application

.Workbooks.Open "C:\My Documents\MySpreadsheet.xls"

.Visible = True

.ActiveWorkbook.RunAutoMacros xlAutoOpen

End With

When you switch to late binding, the intrinsic constant xlAutoOpen is lost with the reference (it just becomes an undeclared variable). You’ll need to know the value of the constant xlAutoOpen so that you can substitute it in your code. Fortunately, when you’ve got the reference set, you can find out the value of the constants by going to the Debug window using Ctrl-G and typing something like the following:

?xlAutoOpen

1

So you’d need to change the code to:

Dim objXL As Object

Set objXL = CreateObject("Excel.Application")

With objXL.Application

.Workbooks.Open "C:\My Documents\MySpreadsheet.xls"

.Visible = True

.ActiveWorkbook.RunAutoMacros 1

End With

A Compile All Modules done after removing your reference will catch all of these issues, provided that you have Option Explicit set to On at the top of your modules.

How can I reduce problems with References when distributing applications?

Once you’ve got your references down to the minimum, it’s time to add the code to your application that will check that the expected reference files exist on the target workstation. Here are some basic rules to remember, based on the advice of Michael Kaplan at www.trigeminal.com/usenet/usenet026.asp:

• First, start with a one-line AutoExec Macro that calls your checking routine: You don’t want Jet’s Expression Service (ES) to get involved yet, as the library that it’s kept in may have been disabled by a missing reference.
• The module that contains your checking routine should only contain routines related to this reference checking and nothing else.
• Your checking routine should only use VBA or Access functionality. Make sure that every function call to VBA or Access is “disambiguated.” This means explicitly indicating the library in which the function exists. For instance, if you’re using a VBA function such as Left$, use VBA.Left$(…) rather than just Left$(…). In that way, no searching for the function will be done: VBA knows to look only in the VBA library. (Of course, you can still run into a problem if the VBA file referenced in the project isn’t present on the target workstation.)
• Don’t load any forms, even hidden ones, until after you’ve checked the references. Have your checking routine call other code to do this once it’s complete.

Michael also recommends that you don’t loop through Access’s References collection, checking the IsBroken property of each member Reference. Rather, he suggests that you try to create any objects that you need through late binding or by checking the Registry. Doing this, of course, requires that you hard-code the logic to check for each specific reference you’re using.

Another approach, perhaps not as foolproof, is to store the details of each referenced file in a table in your application. Yes, the References collection does contain the details of each Reference object, but only when there’s no problem with the References: When a Reference is broken, you lose the ability to access some of the information about that Reference. If you store the details of the files you’re referencing on the development machine, then when you check whether there are any problems with the references on your user’s workstation, you’ll know what the file should be if the reference is broken.

Tony Toews has a free version checker that works this way. You can download it from www.granite.ab.ca/access/ocxdllversionchecker.htm. It allows you to record what versions you’re distributing with the application, as well as what versions were present after you installed. While it doesn’t have the ability to repair the problem, it does identify when there is a problem, allowing you to install the appropriate file(s).

Still another approach is the References Wizard developed by Terry Kreft, which is available (also for free) from www.mvps.org/access/modules/mdl0022.htm at “The Access Web.” This can be installed as an Access add-in, which you can then invoke through the Tools | Add-Ins menu option, or you can call it from your program (instructions are included in the downloadable file). While it doesn’t store details of what referenced files should be present, it attempts to correct broken references by trying to re-register them using their GUIDs.

Finally, Peter Walker has an interesting approach that you can download (for free) from www.papwalker.com/dllhell/index.html. Peter not only stores the details about each reference, he stores a binary copy of the file as well! In this way, if a broken reference is found, the file is written to the same folder as the application, and a reference is set to it. Note that this does have the potential of impacting other applications, since it can change what version of the file is registered in the Registry.

A couple of comments: It’s not possible to repair References in an MDE. If you have a reference problem with an MDE, your only option is to ensure that the correct versions of each file exist in the correct locations. And, while I haven’t tested it, I believe that it’s possible to repair References in an MDB even if your users only have the runtime version of Access installed.

That’s it for this month. Hopefully I’ve given you enough tools to help you with this annoying problem. (By the way, if you download the database associated with this column, you’ll find therein all of the links cited in this article, so you won’t need to key them to get to the sites.)

 Other Pages On This Site You Might Like To Read

Auto-Generated Update SQL
File Open Dialog Options