disappearing Built-in functions

[Guest]

I have noticed that built-in functions such as date() seem to work at times
and not at others. I develop and maintain databases for a variety of
non-profits. They may have any of the Access versions on their computers
(2000, 2003, 2007).

A specific workstation will find that the built-in function (generally the
problem is with the date() function) will not be recognized on database-A.
If they open database-B the function works fine.

If I open database-A from a different workstation, it will not have a
problem with the date() function.

In the past (and with earlier versions of Access) the function would work
for several months... then one day it would stop working. The cure at that
time (with Access 97) was to unistall Access 97... clear out all folders
retated to the installation of Access 97... then re-install. A normal
reinstall without the extra steps would not remove the problem.

Now that my clients are migrating to 2003 and 2007, the problem seems to be
a lot more frequent. Why hasn't Microsoft addressed this problems? Surely
I'm not the only one having problems.

 

['69 Camaro]

Hi, Glen.

They may have any of the Access versions on their computers
(2000, 2003, 2007).

There's your problem. Different versions of Access use different binaries.
Therefore, recompiling the code after opening the database file in a
different version of Access (and fixing any missing references) will fix the
problem. Consider converting to Access 2000 MDE and distributing that to
avoid recompiling issues.

If I open database-A from a different workstation, it will not have a
problem with the date() function.

There's your other problem. Your users are sharing a multiuser database,
instead of using their own copy of the front end of a split database. One
user opens the file on his workstation using a certain configuration for the
library paths, then another user opens the file on his workstation, and
those paths aren't the same, which creates "missing references." When there
are missing references, even the normal built-in VBA functions don't work.

The database should be split, with the tables and relationships in the
database file that is on the networked server, and the forms, queries,
reports, et cetera, should be in the front end. A copy of the front end
should be on each user's workstation. That way, the front end will always
look for its owner's library paths for these libraries. For more
information on splitting the database, please see the "Split the Database"
tip on the following Web page:

http://www.Access.QBuilt.com/html/gem_tips1.html#SplitDB

Now that my clients are migrating to 2003 and 2007, the problem seems to
be
a lot more frequent.

Of course. Big changes were made in the 2007 version. Even the DAO library
is different. Recompiling the code is a must when switching versions if the
file isn't an MDE database file.

Why hasn't Microsoft addressed this problems? Surely
I'm not the only one having problems.

Microsoft has. The Microsoft Access development team has identified the
number one cause of database corruption is sharing a multiuser database
across the network. They recommend that multiuser databases be split for
this and many other reasons, such as record contention, performance issues,
and ease of database application updates.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Douglas J. Steele]

Welcome to DLL Hell! <g>

All Access applications have references to external files. This is a good
thing: it means that Access doesn't have to be as large, and that you're
using common procedures in all Windows applications. Unfortunately, though,
sometimes the references get broken by other applications being installed,
so that the users no longer have the same version of the shared libraries as
on the machine where the application was created.

Take a look at http://www.accessmvp.com/djsteele/AccessReferenceErrors.html
or my December, 2003 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column (and sample database) for free
from http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Guest]

Wow... it all makes sense... now. I have been splitting the databases so
there is a front end. I've not been consistant in making sure the frontend
was on the workstation. That may explain why the locations where I did do
that don't seem to have the problems.

This is my first time going to a community website even though I've been
working with computers since 1868. It was great to get the responses and
actually see other postings with the same problems. I'm amazed people take
the time to monitor and respond to questions.

Thanks!

 

[Guest]

Thanks for your response... after I posted the question, I saw you responded
to someone else with the same question/problem. I had seen references to
this issue when I used Access help... I didn't understand why it was a
problem though. Your (and other) explanations helped a lot.

This is my first venture into a communty even though I've been dealing with
computers since 1968. I'm very impressed with the community support.

Again thanks!

 

['69 Camaro]

Hi, Glen.

This is my first time going to a community website even though I've been
working with computers since 1868.

Wow! You're almost as old as my Grampa would be if he were still alive! He
was born before the Civil War, but he died when I was a teenager.

It was great to get the responses and
actually see other postings with the same problems. I'm amazed people
take
the time to monitor and respond to questions.

We know how hard it can be to solve some of the problems without hints from
others who have already been in our shoes, so it's worth it to share the
information with others so that they can solve their immediate problem and
pass that knowledge on, too. It's a part of "giving back" what's been
shared with us. And it's a lot of fun solving puzzles.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Hi Glen,

...since 1868 ? <smile>

In addition to the information that Gunny and Doug have provided, here is an
article that you may find helpful:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks... I have been creating split databases for quite a while, however
I've not really known what I can do to improve the performance (speed). I've
come up with some ideas... though not many. The recommendation (and link)
provides some good tips.

Okay, it only seems like since 1868 (unit record equipment days and
Burroughs 100 then the leap to IBM System 3) but I will fess up and say it
was 1968.

 

[Elisabethkatie]

Wow... it all makes sense... now. I have been splitting the databases so
there is a front end. I've not been consistant in making sure the frontend
was on the workstation. That may explain why the locations where I did do
that don't seem to have the problems.

This is my first time going to a community website even though I've been
working with computers since 1868. It was great to get the responses and
actually see other postings with the same problems. I'm amazed people take
the time to monitor and respond to questions.

Thanks!