'Function is not available' message

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?
dsoderstromAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< 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
 
Rgonzo1971Commented:
Hi,

Is the reference to

Microsoft Jet and Replication Objects Library ticked?

Regards
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
dsoderstromAuthor Commented:
MS Access is not installed on the computer, only access runtime.  I can not open the database to look at references.
0
 
dsoderstromAuthor Commented:
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
 
Dale FyeCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
 
dsoderstromAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
 
dsoderstromAuthor Commented:
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
 
dsoderstromAuthor Commented:
One more thing ......

I also tried packaging the application from a Windows XP computer.  Same problem
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
 
dsoderstromAuthor Commented:
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
 
dsoderstromAuthor Commented:
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
 
dsoderstromAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
dsoderstromAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.