Visual Basic for Applications Project in the database is corrupt

[OrphanAnnie]

We have a shared (but not split) database on the network. One user
suddenly started receiving this error message, but only when she
changes an inserted subdatasheet in a table. The user can continue to
work IF she clicks thru the error messages, anywhere from 3 to 10 or
more times. Since she must constantly change subdatasheets due to the
nature of her work, she has to stop and do this frequently.

I know the database itself is fine because other users are having no
problem at all. We reinstalled the software on this user's local
machine, but that didn't help. The database was created in A2003 and
is being used in that version, so it's not a conversion issue. We
don't want to split the database because in our firm we sometimes have
to swap out PCs without advance notice (our users store everything on
the network).

I saw a post on this dated a couple of years ago but there were no
solutions that seemed to apply. The database is usable but the person
doing the data entry is getting frazzled. Has anyone run into this,
or does anyone have any ideas? Thanks....

 

[George Nicholson]

Apologies in advance for the bluntness herein.

Has anyone run into this, or does anyone have any ideas?

yep, its in the following list:

Some Microsoft Access MDB Corruption Symptoms
http://www.granite.ab.ca/access/corruption/symptoms.htm

We have a shared (but not split) database on the network.

If multiple users are working in the same database file at the same time,
you are using Access in a manner for which it was NOT designed and
corruption *will* occur sooner or later.

I know the database itself is fine because other users are having no
problem at all.

Sounds like you've been lucky. That will change. Just a matter of time.

We don't want to split the database....

Then your expectations for the database to work properly should be set
extremely low.


Corrupt Microsoft Access MDBs FAQ
http://www.granite.ab.ca/access/corruptmdbs.htm

Splitting your Microsoft Access MDB into a front end and back end
http://www.granite.ab.ca/access/splitapp/index.htm


HTH,

 

[OrphanAnnie]

Apologies in advance for the bluntness herein.


yep, its in the following list:

Some Microsoft Access MDB Corruption Symptomshttp://www.granite.ab.ca/access/corruption/symptoms.htm


If multiple users are working in the same database file at the same time,
you are using Access in a manner for which it was NOT designed and
corruption *will* occur sooner or later.


Sounds like you've been lucky. That will change. Just a matter of time.


Then your expectations for the database to work properly should be set
extremely low.

Corrupt Microsoft Access MDBs FAQhttp://www.granite.ab.ca/access/corruptmdbs.htm

Splitting your Microsoft Access MDB into a front end and back endhttp://www.granite.ab.ca/access/splitapp/index.htm

HTH,

Thanks for the references -- I had already read most of them. Luckily
one of my co-workers realized the problem might be with this
particular user's roaming profile since she had the same problem no
matter which station she logged in from. When her roaming profile was
recreated and all cached profiles were deleted, the problem
resolved.

I agree that splitting the database would be ideal, but because of our
network setup (we run Citrix, and Access is one of the few apps that
run outside it) we never store any data on local stations and that
makes splitting a last resort for us. Luckily we only have 3 users,
with only one in the DB at a time, and we backup every time the DB is
used, so I'm hoping we'll be okay going forward.

 

[Paul Shapiro]

You would leave the data (in the back-end of the split database) on the
server. Each user should be running a separate copy of the front-end, with
the queries, forms, reports and code. I find it easiest to setup a batch
file that copies the current front-end to the user's local computer and then
runs that local copy. If you're running in Citrix, it would just copy the
latest front-end to the "local" storage for the current user session. Even
if that's a network location, at least each user has a separate, unshared
copy of the front-end. Nothing volatile is on the user's computer, just a
temporary copy of the front-end.

Hoping things will be ok when you know that your current operating procedure
increases the risk of data corruption seems too much like wishing. In this
case it's easy to do better, definitely a lot less work than fixing
corrupted data or explaining why data disappeared. I sometimes ask clients
how much they would be willing to pay to fix a problem, and then suggest it
makes sense to spend up to that on minimizing the likelihood.
Paul Shapiro

 

[Tony Toews [MVP]]

I agree that splitting the database would be ideal, but because of our
network setup (we run Citrix, and Access is one of the few apps that
run outside it) we never store any data on local stations and that
makes splitting a last resort for us.

But if you implement a system that copies down a fresh copy of the FE
as required then that's no big deal. You would store the FE MDE/MDB
in the %APPDATA% folder or C:\Documents and
Settings\ttoews\Application Data on my system.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

Luckily we only have 3 users,
with only one in the DB at a time, and we backup every time the DB is
used, so I'm hoping we'll be okay going forward.

Hopefully.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/