Date() and Access 2007 Runtime

[Rose B]

I have an application front end which was written in Access 2002 but seemed
to work OK in 2007 Runtime simulation so decided to convert to 2007 and then
to install Runtime 2007 for those users without Office 2007 and have them run
the application under that (XP OS). Conversion OK, and application works fine
under full Access 2007, but under 2007 Runtime errors occur where Date() is
used either as part of a query or default value for field on a form. Am
experimenting by using Now() instead (which appears to be more reliable) but
wonder if anyone has any other ideas as this is quite tedious since this is a
large application.

 

[Stefan Hoffmann]

hi Rose,

Conversion OK, and application works fine
under full Access 2007, but under 2007 Runtime errors occur where Date() is
used either as part of a query or default value for field on a form. Am
experimenting by using Now() instead (which appears to be more reliable) but
wonder if anyone has any other ideas as this is quite tedious since this is a
large application.

This sounds more like a broken reference than a bug. You may also scan
your modules after a user-defined function called Date().


mfG
--> stefan <--

 

[Douglas J. Steele]

hi Rose,



This sounds more like a broken reference than a bug. You may also scan
your modules after a user-defined function called Date().

As Stefan says, it's likely a References problem.

While you're trying to resolve the References issue, see whether using
VBA.Date() instead of just Date() works.

 

[Rose B]

See later post - re SQL syntax issues too

 

[Rose B]

Douglas/Stefan,

thanks for your comments,

I too was wondering about a broken reference but how do I check this on an
XP machine running 2007 Runtime? Do I repair? (This machine is running 2002
Professional and I have mdb/mde file types launching Access 2002 but accde
launching 2007 Runtime - could this be my problem?)

I have actually been all the way through and replaced Date() with Now() or
DateValue(Now()) - but will try the VBA.Date idea at a later date. What I am
finding now is that a couple of forms with On Click events I get errors on
the first click (function not available) but it works on the second time
around.

 

[Douglas J. Steele]

It's a lot harder to resolve reference problems using the runtime, since you
cannot get to the References dialog.

Basically, what you have to do is, on a machine with the full version of
Access, determine the location of each referenced file, and the exact
version. Then, on each client machine where it's not working, you have to
ensure that you've got each the same version of each of the referenced files
in the same location. I talk about this in
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

 

[Rose B]

Thanks. What I did was to write an inno script to put the reference files
from my PC with full Access 2007 installed into the same directories etc. on
the PC where the Runtime is installed, and which had the problems. I had
noticed that there were some differences in the versions and since there were
7 reference files needed thought this might be a quicker/safer option.

SOME of the problems have gone..... but I have found a few places where I
hadn't changed the Date() and this is still causing an issue. I took the
opportunity to try and replace these with VBA.Date() but as they are in the
query selects as the data source for a report this does not work. Guess I
just need to go through all of the reports now but this is a real pain!!!
Just cannot understand why Date() works OK in the full version and not in the
Runtime - any more ideas always welcome, and I would try them and see if they
work, just in case this helps others in the future.

 

[Douglas J. Steele]

What references are you using? Seven seems like a lot: perhaps we can pare
the list down for you.

 

[Rose B]

They are:-

VBE6EXT.OLB
ACEDAO.DLL
MSOUTL.OLB
MSO.DLL
stdole2.tlb
MSACC.OLB
VBE6.DLL
msado21.tlb

(Just realised that there are actually 8 so I need to double check my PC and
retest! MSACC.OLB was the one I forgot to include). The application
automatically sends Outlook e-mails, hence the Outlook one. Any insight
gratefully received - I really do appreciate the time you are spending.
(Apologies if I am a bit slow in getting back, I don't seem to be getting
e-mail prompts to tell me a post has been made).

 

[Rose B]

Following from last post - have re-checked and all 8 have been checked but
problem still persisted with Date().

I notice that in VB Date() is now Date - and it is only in objects (queries,
default values etc.) that the problem occurs. Is there some incompatibility
here between 2007 full and Runtime?

 

[Douglas J. Steele]

I doubt it's an incompatibility (since they're both the same executable)

Looking at your references in the other post, why do you have have the
reference to Office (MSO.DLL)? And for the reference to Outlook
(MSOUTL.OLB), have you considered using Late Binding instead? Once you do
that, you can remove the references.

See whether your application will still compile if you remove the references
to VBE6EXT.OLB, stdole2.tlb and msado21.tlb. If it does, you can be 99.9%
certain you don't need them.

 

[Rose B]

Genius!!!

I managed to remove VBE6EXT.OLB, MSO.DLL and stdole2.tlb (msado21.tlb could
not be removed - caused a problem with connections) and compiled. Then I
replaced one of the Date() entries that I had removed before and put it onto
the XP PC - and it worked. It is quite a large/complex application (much of
which I inherited) so will take me a wee while to test.

Will have to investigate pros/cons etc. of Late Binding (new one for me) -
but at the moment guess that if it ain't broke ........

Why would removing these references have had this effect?

Thanks so much for your help. (I will now check the references in another
application that I have built from scratch - and I think uses Date() - and
see if that is OK under Runtime.

Thanks again

Rose

 

[Douglas J. Steele]

What happens whenever Access encounters a function it doesn't know about
(and Access doesn't actually know about the Date function: it gets it from
the VBA library), it searches through all of the referenced libraries until
it either finds the function or finds a broken reference. While usually this
search is done in the same sequence as the files are displayed in the
References dialog, Access actually searches in the VBA library last. (This
is done deliberately so that you, or other library developers, can override
built-in functions should you need to, although personally I don't think
that's a good idea). That means that whenever any reference is broken and
Access tries to use a VBA function, it'll run into problems.

Now, qualifying the function with the library information (VBA.Date, as
opposed to just Date) should work, because Access won't search through all
of the libraries, just the one you specified. However, things work
differently when you're running queries. What you could have done, I
suppose, is create your own function (call it MyDate) that simply calls the
VBA Date function (qualifying it as VBA.Date), and then call your function
in your queries, rather than calling Date, but you're far better off
eliminating the problem, rather than creating work-arounds.

In my opinion, Late Binding is always a good idea. I work in an environment
where everyone's desktop is supposed to be the same, but even here I use
Late Binding whenever I can. Some people complain that it's slower, but
realistically it should only be the instantiation that'll be slower, and if
you're doing it properly, that won't add up to much of a delay. Tony Toews
has a write-up about Late Binding at
http://www.granite.ab.ca/access/latebinding.htm if you need how-to
information.

 

[David W. Fenton]

While usually this
search is done in the same sequence as the files are displayed in
the References dialog, Access actually searches in the VBA library
last. (This is done deliberately so that you, or other library
developers, can override built-in functions should you need to,
although personally I don't think that's a good idea).

I think it's a very good idea. And you can avoid any performance
issues by specifying the parent of a function/sub/property so that
if there's a name conflict with VBA, it doesn't have to search.

In the app I'm working hardest on right now, I've got a function
called OpenForm(). It's very complex. But it doesn't conflict with
anything because the Access function is a member of the DoCmd
object. You could do the same with Round() and Replace(), if you
want, i.e., having your own custom version, and then specifying
VBA.Round() and VBA.Replace() when you wanted to use the built-in
versions.

 

[Tony Toews [MVP]]

Thanks. What I did was to write an inno script to put the reference files
from my PC with full Access 2007 installed into the same directories etc. on
the PC where the Runtime is installed, and which had the problems.

However that very likely won't help your problems as frequently those
files need other files and/or need registering, etc, etc.

SOME of the problems have gone..... but I have found a few places where I
hadn't changed the Date() and this is still causing an issue.

Fixing the Date problems means you're only touching the surface of the
problem and not the real problem.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Tony Toews [MVP]]

Will have to investigate pros/cons etc. of Late Binding (new one for me) -
but at the moment guess that if it ain't broke ........

But the references will break as soon as you attempt to run your app
on a system with a difference version of Outlook. There are very
few cons with late binding. My page describes those.

They are mostly a matter of not being able to use Intellisense while
developing. Fixing that is a matter of commenting and uncommenting
about three lines of code.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/