Solved

Date function error in Access application

Posted on 2008-10-27
7
919 Views
Last Modified: 2013-11-27
We have a commercial application developed in MS Access 2003 which has been successfully operating for 12 months during which there have been a number of updated versions. Th app. has a frontend and a backend database. It is distibuted on CD with MS Setup Bootstrapper and installs MS Access Runtime. In our latest update we find that Date functions in SQL queries are causing the following error:
'Function is not available in expressions in query expression 'Date()'
on XP machines (both SP2 and SP3 and we THINK with no MS Office applications installed). This type of query is causing the error:
"DoCmd.RunSQL "UPDATE School SET School.YrGpUpdated = Date();"
if we change the Date function to the Now function, the query works - but we have a lot of Date functions and want to avoid changing them all.  This current version has been develoepd in the same version of MS Access; it has the same DLLs and references as the previous version; the same Package and Deployment and Runtime version. Any ideas on why this might suddenly be happening?
0
Comment
Question by:Al230762
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
I would say that it is almost certain that this is a references problem.
Difficult to diagnose in a runtime but you can use code in your app startup to check for missing refs.

Dim ref As Reference    
'Find the DAO reference    
For Each ref In Application.References        
If ref.IsBroken Then            
MsgBox ref.Name & " : Is Missing/Broken!"        
End If    
Next ref
0
 

Author Comment

by:Al230762
Comment Utility
peter57 - I agree it seems like a reference problem. I tried your suggestion on the target machine where the problem is occurring and even added a positive confirmation that each reference was OK:
If ref.IsBroken Then
MsgBox ref.FullPath & " : Is Missing/Broken!"
Else
MsgBox ref.FullPath & " : Is OK!"
End If
Next ref

all references were shown as OK. Any other ideas?
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
I would still opt for a refs problem - but maybe it is a file version difference rather than a missing file.

Are you using an mdb or an mde in your runtime?
0
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.

 

Author Comment

by:Al230762
Comment Utility
We use an mde developed in Access 2003. When I take the mde from a previous build of the application and place it on the target machine, that works fine. When I open and re-compile that previous build on my development machine and then place that new mde on the target machine it behaves exactly as the current version, i.e with errors. Things have obviously changed on my development machine between the previous build and the current build and yet all the references appear to be unbroken on the target machine for both the previous build and the current build.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 200 total points
Comment Utility
Well obviously an mde is more picky than an mdb when it comes to references.
But all i can think of now is to go through each ref in turn and locate the file and check the version numbers between the two machines.

If the target machine has no office software installed then it won't be getting updates, I would assume.
0
 

Author Comment

by:Al230762
Comment Utility
The target machine I am testing on is a virtual machine with XP Pro SP3 and all the latest updates but no MS Office at all. We have customers with this same setup and they have reported the problem to us.

We have now replaced all instances of the Date() function with Now() and placed the new mde on the test target machine. It works fine! We are continuing to test but it seems as if other likely candidates to crash e.g Left(), Nz, Mid, Right etc are all functioning OK. We cannot find any problem other than Date().

I'm sure you are right in that it's related to updates and/or references but it's going to be hellish long job for us to discover what the precise problem is. I'd like to know if anybody else has had this problem.
0
 

Accepted Solution

by:
Al230762 earned 0 total points
Comment Utility
It appears that my development machine had recently applied the Office 2003 Service Pack 3. On compiling the application and then deploying on a machine without any Office product installed or without Office 2003 SP3 installed, the Date() function produced an error message. Answer was to remove any Date() functions, particularly in queries, and replace with Now() - just another of Microsoft's little surprises. I suspect that the VBE6.DLL may be the culprit and that this should possibly be applied to the target machine but I'd rather change my code than so that.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

763 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

13 Experts available now in Live!

Get 1:1 Help Now