problems with Access 2003 query

[Techacct1]

We are running MS Access 2003 on citrix servers. We have a bunch of access
databases that query against Oracle and were working fine when the servers
were running MS Access 2000. We built new servers, did clean install of
Access 2003. When non-administrator users run the query they get "Undefined
function <name> in expression. (Error 3085)You entered an SQL expression that
includes a Function procedure name that cannot be recognized. Make sure the
function exists, that it can be used in SQL expressions, or check the
expression to make sure you entered the name correctly"

However, the query works fine for administrators on the server. Any ideas
where to look?

 

[John W. Vinson]

We are running MS Access 2003 on citrix servers. We have a bunch of access
databases that query against Oracle and were working fine when the servers
were running MS Access 2000. We built new servers, did clean install of
Access 2003. When non-administrator users run the query they get "Undefined
function <name> in expression. (Error 3085)You entered an SQL expression that
includes a Function procedure name that cannot be recognized. Make sure the
function exists, that it can be used in SQL expressions, or check the
expression to make sure you entered the name correctly"

However, the query works fine for administrators on the server. Any ideas
where to look?

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

If not please post the SQL of one of the offending queries and indicate which
function it's not finding.

 

[Howard Burgman]

We are running MS Access 2003 on citrix servers. We have a bunch of access
databases that query against Oracle and were working fine when the servers
were running MS Access 2000. We built new servers, did clean install of
Access 2003. When non-administrator users run the query they get
"Undefined
function <name> in expression. (Error 3085)You entered an SQL expression
that
includes a Function procedure name that cannot be recognized. Make sure
the
function exists, that it can be used in SQL expressions, or check the
expression to make sure you entered the name correctly"

However, the query works fine for administrators on the server. Any ideas
where to look?

 

[Techacct1]

Yes, that seems to fix it! But will this apply to all users of the database?
What causes this issue in the first place?

 

[John W. Vinson]

Yes, that seems to fix it! But will this apply to all users of the database?
What causes this issue in the first place?

It will fix the problem *on that machine*. You may need to check the
references on every machine on which the frontend is running.

The "References" are pointers to three essential DLL code libraries:

Visual Basic for Applications
Microsoft Access <version> Object Library
Microsoft DAO <version> Object Library

It may have references to other libraries - optional things like ADO, ADOX,
OLE Server; add-ins you've installed; references to other Office programs such
as Excel and Word. If *any* of these are referencing some DLL library that
doesn't exist, or isn't found where it's supposed to be, you'll get the
MISSING message for that library, and *none* of the libraries which are there
can be counted on to work correctly.

I'd suggest paring down the references to the minimum required set. Back up
your database first, then start deleting extra references. Compile your
project after you delete each reference to be sure you haven't broken
something. If some code fails to compile after removing a reference, try to
determine whether the code is actually necessary (it might have been something
experimental that isn't actually part of the application).

Good luck!