Function is not available 2

[vich]

I am getting the dreaded "function is not available in query expression format....." I am distributing the application to multiple PC's as an MDE developed in Access 2003. A couple will work, most get the error.

I have read many posts and tried comparing all library references between each of the machines and my development PC. I found differences in the VBA and the MSComctl.ocx. I copied the versions from the development PC to the others and registered them via regsrv32 and still get the error even though the version #'s are the same.

Anybody have any other ideas. I am stuck and can not deliver a major upgrade.

Thanks

 

[PHV]

I think that the MDE must be compiled with the same version of access as the target.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vich]

The targets are using runtime package which was created from the development machine several months ago. Now all I do is create a new MDE to distribute. That is why it is so confusing.

We do have another app that runs a msaccess runtime but all machines have been loaded basically the same.

Thanks for taking the time to think about this. I'm really stumped.

 

[PHV]

Shouldn't you recreate the package ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vich]

Well I tried that and got a error referencing rpcrt4.dll in the dump it wants to send to Microsoft. So now I am trying to figure out what this problem is as well.


It seem every logical approach becomes problematic.

 

[BMeek]

vich,
How are you distributing the database? Did you build an installation routine or are you simply copying the file from a CD to the new hard drives?

If you are copying the file via a CD, make sure the file is copied to the hard drive and not being run from the CD. Even if the database is compiled as an .MDE it may have set the 'read only' flag. If you have code behind some form tools that needs to copy or save data the computer can't write back to the CD disk.

By creating an installation package and installing the database as a program you can still have some issues between file versions on the developmental machine and the target PCs. Make sure any referenced file libraries are available on the target PCs. Here is a bit of info I found when researching the same issue:

IMPORTANT NOTE ON MDE FILES: The following methods work only in standard Access files (.mdb). You cannot refresh references in an Access MDE (.mde) file. References must be refreshed in the original .mdb file before being recompiled into a new .mde.


SYMPTOMS
When you run a query, you may receive the following error message:
Function isn't available in expressions in query expression.
You may see a "#Name" error on forms and reports in controls that use an expression for the ControlSource property. This behavior can occur on forms, on queries, or on reports that use built-in functions in expressions, for example, the Format(), Left(), or Right() function.
CAUSE
You developed a database by using a particular version of a type library, and then upgraded the version of the type library on your development computer.
-or-
You referenced a type library on your development computer, and then moved the database to a computer that does not have the library, or has a newer version of the library.

NOTE: This issue often involves references to Comctl32.ocx. But this issue can also occur with other type libraries. An example of installing a newer type library version is installing newer versions of Microsoft Data Access Components (MDAC). Sometimes the upgrade of a type library is subtle, such as when you upgrade the operating system version or install a new application.
RESOLUTION
To fix this problem, you must have the same version of the type library on your development computer and on other computers that will be using the database. Or you must refresh the reference to the type library on the computers that will be using the database. You must do this so that Microsoft Access can recognize the control. What follows are three methods that you can use to do this.

The Common Dialog Control OCX
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.

 

[vich]

Wow the most comprehensive answer I found regarding this topic yet. I had searched many forums and posts extensively. It is/was an library not a "read only" issue.

I wish I had gotten your suggestion earlier. I had just paid Microsoft for support and discovered exactly as you said with a twist however.

What I found is that I had started referencing the Outlook Library which was to only be used by one user that already had Outlook. What was happening was several other users were getting errors relating to the Format function. Additionally, some of the ones that did not get the error did not have Outlook installed either. So.... I guess even though the library wasn't being used it's absence caused problems for other functions in other libraries.

I am still working with Microsoft to resolve why I get an error when creating a package now. The program crashes and the dump references the rpcrt4.dll. Don't know if you have had an experience with that problem as well.

Anyway, awesome response, here's a couple of stars.

Thanks

 

[BMeek]

vich,
Only someone who has suffered similarly can appreciate your frustrations with seemingly random errors only on certain machines. Thanks for the stars!

I develop a distributed program using Access 2000 and have been through the ringer a number of times due to "Function not available..." My database product involves distributing the program quarterly via CD to clients all over the country with so many varieties of Windows and Office configurations that it makes your head spin!

The biggest issue with "Function not available..." has been whenever I tried to reference a particular Office product library that has many, many versions in the general marketplace. Whether it is Excel or Word or Outlook my best advice is DON'T!

For a couple of years I included the same version of MDAC tools in my installation package just so I could install and reference the matching versions from my developmental PC. I've included a Global Declarations module in my program that uses a 'Check References' and 'Fix References' routines so that everytime I build a new file it checks and fixes any broken reference links in the new file. However, I still get errors occassionaly. Also since I distribute an .MDE file there is no way to update references in a compiled file. You have to go back to the original .MDF and then rebuild the .MDE and reburn a CD...

I've used about 6 of my company's Microsoft Help Tickets from our MSDN subscription over the past 5 years on solving many of the odd behavior issues we get on certain PCs. My challenge is our clients are not technically savvy and often don't know how to install the program much less troubleshoot any errors. But, without technically unskilled users I wouldn't have a job because a simple to use point and click front end for our database wouldn't be needed!

I have been testing the possiblility of migrating to Access 2003 and I'm pretty gun-shy about making the change due to these past issues everytime a small change is made to the support files. I have limited 'test' machines to try and debug all the environmental possibilities so I will continue testing as best I can. I try to find 5 or 6 users of our product who have some technical skills and send them a beta test. That's a good thing but many of them move on to other jobs or positions along the way. Such is the life of an Access programmer!

Thanks again for the stars!
Bryan

 

[PHV]

I tried to reference a particular Office product library
Why not using late binding (no reference needed...) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886