Solved

'Function is not available' message

Posted on 2014-01-13
17
389 Views
Last Modified: 2014-02-03
I am in the middle of replacing several Access 2003 applications with Access 2010 versions.  None of the users have MS Access installed so I package and deploy these applications along with Access 2010 runtime.  I first remove all of the old 2003 applications and access 2003 runtime then install the new applications.   The workstations are a mixture of Windows 7 and Windows XP.
   On one of the Windows XP workstations I get the following message when trying to run some options in the new application:

Function is not available in expressions in Query expression 'Date('

  The new application runs fine on other XP workstations.  Any ideas on what the problem could be and how to fix it?
0
Comment
Question by:dsoderstrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 58
ID: 39776541
<< Any ideas on what the problem could be and how to fix it? >>

 You have broken references.  Open a code module, go to tools/reference and fix any listed as MISSING or BROKEN.

Jim.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39776557
Hi,

Is the reference to

Microsoft Jet and Replication Objects Library ticked?

Regards
0
 

Author Comment

by:dsoderstrom
ID: 39776576
MS Access is not installed on the computer, only access runtime.  I can not open the database to look at references.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:dsoderstrom
ID: 39776614
I came up with some code that will allow me to display the references from within the application.  I will add a command button in the application to run this code and display the references and then redeploy it to the workstation causing the problem.  Then I should be able to see if any of the required references are missing.
   If it turns out that one of the references is missing, how do I fix it?  Is it as simple as copying the the .dll source file for the reference to the proper location on the computer or won't that work?
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39776681
If you can do the packaging of the file on an XP workstation rather than a Win 7 workstation, you might be able to avoid the references issues.  References generally scale upwards well, but not backwards.
0
 
LVL 58
ID: 39776735
<<MS Access is not installed on the computer, only access runtime.  I can not open the database to look at references. >>

  Then you either need to look at it on a station with a full version, or add code to the app to list the references and what's broken at start-up.  Then go from there.

But something is broken.

 I would suggest in addition to what Dale said, taking the DB and under a full version, eliminate all possible reference (uncheck, try a compile) and get down to the minimum set of references.

Jim.
0
 

Author Comment

by:dsoderstrom
ID: 39777131
Had to go out of the office on a project so will have to get back to this tomorrow.
One question though.  Would it really help to look at this database on another computer?  Wouldn't a missing reference problem be specific to this computer.  As mentioned previously, the application works fine on other XP computers.
I did add code to the app to list the references so will redeploy the app to the problem computer and check the references when I get back to the office.
0
 
LVL 58
ID: 39777185
<<One question though.  Would it really help to look at this database on another computer? >>

  Yes and no.   yes in that you can eliminate some references that may not be used or spot something that needs to be installed that your using (like vbSendMail.DLL).

<<Wouldn't a missing reference problem be specific to this computer. >>

 Yes, that's true.

<< did add code to the app to list the references so will redeploy the app to the problem computer and check the references when I get back to the office. >>

 That's the ticket.  Make sure you include the .IsBroken proeprty so you'll know which reference(s) it is.

Jim.
0
 

Author Comment

by:dsoderstrom
ID: 39780035
1)  I added the Microsoft Jet and Replication Objects  reference as suggested above.
2)  Added a command button to the application for checking references.
3)  Redeployed the application to the problem computer.
4)  Checked the references.  All of them appeared and there were no broken ones.
5)  Still got the same error message.

Pondering my next step.
0
 

Author Comment

by:dsoderstrom
ID: 39780382
One more thing ......

I also tried packaging the application from a Windows XP computer.  Same problem
0
 
LVL 58
ID: 39780905
<<1)  I added the Microsoft Jet and Replication Objects  reference as suggested above.>>

  You don't need these typically.  uncheck the reference, do a compile.  If you get a compile with no errors, then you don't need the reference.

<<Pondering my next step. >>

Two possible things depending on the libs your using, do you use DAO and/or ADO?

For DAO, you may need to re-register DAO on the target machine.  From a command prompt, do:

regsvr32 -u "c:\program files\common files\microsoft shared\dao\dao360.dll"

Followed by:

regsvr32 "c:\program files\common files\microsoft shared\dao\dao360.dll"

regsvr32 is found in C:\Windows\System32

If your on a 64 bit OS, you will need to use the one in C:\Windows\sysWOW64  assuming you have the 32 bit edition of Office as well (that also may be part of the problem).

With ADO, there was a bug a while back which you might be tripping over (don't think so because you moving from old to new, but it doesn't hurt to double check):

An ADO-based application that is compiled in Windows 7 SP1 or in Windows Server 2008 R2 SP1 does not run in earlier versions of Windows
http://support.microsoft.com/kb/2640696

Jim.
0
 

Author Comment

by:dsoderstrom
ID: 39788378
Sorry for the slow response.  Have been away from the office working on another project.
In response to your question Jim, I am using DAO.  I will try your suggestions regarding that next Tuesday when I get back to that office.  Thank You
0
 

Author Comment

by:dsoderstrom
ID: 39803919
Finally got a chance to try re-registering DAO on one of the computers I was having a problem with.
Still get the same error message (see original post).
This error message seems to refer to the Date() function.  I use this extensively through the applications to return the current date.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39804168
In some way, it's a broken reference.  This is not unusual and is a long standing problem:

ACC97: Error Message: Function Isn't Available in Expressions in Query Expression
http://support.microsoft.com/kb/194374

 let me think about this a bit...

Jim.
0
 

Assisted Solution

by:dsoderstrom
dsoderstrom earned 0 total points
ID: 39815991
I found a way to solve my problem.  It's probably not very elegant and should not be necessary but what I did is add the code shown below to the startup routine of my applications.  All it does is remove the last reference in the list and re-add it.  It doesn't seem to matter which reference I refresh and I only need to do one of them.
Jim, I'm awarding the points to you because you kept me on the right track regarding a reference problem and offered a lot of help.  Thank you.

Private Sub RefreshRef()

    Dim loRef As Access.Reference
    Dim intCount As Integer
    Dim strPath As String
   
    On Error Resume Next
   
    'Count the number of references already referenced
    intCount = Access.References.count
   
    'Get the last reference, remove it, then add it back.
    Set loRef = Access.References(intCount)
    With loRef
        strPath = .FullPath
        With Access.References
            .Remove loRef
            .AddFromFile strPath
        End With
       
    End With
   
   
End Sub
0
 
LVL 58
ID: 39816001
First, I'm sorry I didn't get back to you.  I've been a bit swamped lately.

Second, I've very happy to hear you figured out a solution!

and third, because of that, I'm going to ask you to close this a bit differently.

I want you to accept your own comment as the solution.  You can if you want give me an assist for pointing you in the right direction, but the solution is certainly yours.

Thanks,
Jim.
0
 

Author Closing Comment

by:dsoderstrom
ID: 39829063
I accepted my own comment as the solution because it was what ultimately solved the problem.  I appreciate the assistance given to me by others that led me towards this solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question