"Function is not available" Error

[Mike C]

I maintain a distributed A2K application that is
experiencing a problem on our NT 4.0 machines. The front
end app is distributed as a compiled MDE front end that
updates a backend A2K database on a server.

Most functions work fine for all users, but when any of
our NT 4.0 users attempt to run (most) reports, an error
message similar to the one below will display:

"Function is not available in expressions in query
expression"

....followed by SQL code from the underlying query.

The same code runs fine, without errors, on all of our
Windows 2000 machines. This is a big clue, but I don't
know how to proceed.

KB #310803 indicates that this error may be caused by a
problem with a programming type library, or with code that
does not specifically call out the correct library. - This
article is the only one I could find that seemed to be
related to the observed symptoms.

I'm guessing that this means that the *PATH* used to find
the file required for the library is not the same in NT
4.0 as it is in Windows 2000...and IF this is the case, I
don't understand how to fix this particular problem.

Has anyone experienced the above error or have any ideas
for a resolution?

 

[Douglas J. Steele]

Sounds like it might be a References problem. These can be caused by
differences in either the location or file version of certain files between
the machine where the application was developed, and where it's being run
(or the file missing completely from the target machine). Such differences
are common when new software is installed. Unfortunately, these can be hard
to fix with an MDE.

For MDBs, the instructions are:

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). 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)

Unfortunately, you can't do this with an MDE. What you can try is opening
the MDB that the MDE is based on, then open up the References dialog and
determine the file referred to by each selected reference. Through File
Explorer, determine the version of each of the referenced files. Now, on the
machine(s) with the MDE, make sure that the same version of each file exists
in the same location. If not, you'll have to do upgrades to ensure that they
are the same (note this could just as easily mean upgrades to the
development machine.)

The following code should help you determine the files being used:

Sub ListReferences()
Dim refCurr As Reference

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

End Sub

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Mike C.]

....and it worked. It was as simple as unchecking a couple
boxes and recompiling. Problem solved. Thanks.