Solved

'Function is not available' message

Posted on 2014-01-13
17
366 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
17 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<< 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

Is the reference to

Microsoft Jet and Replication Objects Library ticked?

Regards
0
 

Author Comment

by:dsoderstrom
Comment Utility
MS Access is not installed on the computer, only access runtime.  I can not open the database to look at references.
0
 

Author Comment

by:dsoderstrom
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:dsoderstrom
Comment Utility
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
Comment Utility
One more thing ......

I also tried packaging the application from a Windows XP computer.  Same problem
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
Comment Utility
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
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
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now