Sudden database corruption of multiple databases.

[Guest]

Help!!

In the last week I've had three totally seperate databases crash. I cannot
repair any of them, even using all suggestions I've seen in the archives.
Two of them are greatly reduced in size thus seem to be missing most of their
data. The common message that appears when you try to open them is:

"The database is in an unexpected state; Access can't open it. The database
is converted from a prior version by using the DAO compact database method
instead of the Convert Database command. This has left the database in a
partially converted state. ...."

These databases are in a shared directory on my computer and accessed by
other people. Two of the crashes happened when another person was closing
the databases after working in them and was thus compacting the databases.
The people received the error message that they did not have the right to
write to my drive (they do). The third just started giving the message upon
opening without any error messages. I've also noticed that "db1.mdb"s have
been created on some of my simplier databases and get the same error messages
if I try to open them. (however the actual databases work fine).

My questions:
Does anybody have an idea why this is happening, especially why would
suddenly start happening to multilple databases? I've read Tony Toews
corrupt mdb's FAQ but still am not sure what would suddenly be causing
problems in databases that have existed without problems since 2000.

The databases are pretty much a number tables and querries although each of
the crashed versions have either a form or report also. Would the lack of a
FE make a difference in such simple databases?

What can I do for prevention to keep my backups and other databases from
having similiar problems?

Is there any chance of recovering data when the database size is about 1/10
the size it should be? I've already tried a database recovery service and it
indicated it could recover unneeded tables.

Thank you with desperation,

 

[Nikos Yannacopoulos]

Karen,

When a database is meant to be a multi-user one, the split to a FE/BE
scheme, with each user having their own (usually local) copy of the FE,
is an absolute must, regardless of the size or complexity of it. The
penalty for not splitting is what you are experiencing now: corruption.
The sad part is, like you also know the hard way now, it may not happen
for a long time, and then hit you out of the blue when you're convinced
there is no reason why it should.

If it helps understand what's happened: whenever Access is asked to
compact an open database, it first closes it, then compacts into a new
one named dbX.mdb (x = 1, 2, 3...) and when the compaction finishes
successfully it deletes the original.mdb and renames dbAX.mdb to
original.mdb, and finally re-opens original.mdb. The only difference
when you compact on close is original.mdb is not re-opened.
Now, if the process fails during the compaction phase, you may have a
useless dbX.mdb while your original.mdb is still intact (though not
compacted). If compaction finishes successfully but the deletion of
original.mbd or renaming of dbX.mdb fails, you will get an error
message, and have a compacted dbX.mdb and, possibly, an uncompacted
original.mdb.

Bottomline, SPLIT BY ALL MEANS and put the back end in a folder that is
backed up regularly. Following the split you won't be able to compact
the back end by compact on close, so you'll either have to do it
manually once in a while, or set up a scheduled job to do it, say, every
night. It's an extra step, but essential in avoiding corruptions.

HTH,
Nikos

 

[Guest]

Thanks Nikos,

I'm still wondering how three unrelated databases would go down within a few
days, the FE/BE seems to be an issue which might of prevented it from
happening but not the cause. any thoughts?

Some of my multi-user databases have nothing but tables in them. Would a FE
still help prevent corruption?

Some of my users are the people building queries, forms and reports (Usually
different people working at different times). How do you deal with FE's,
when the very process the people need access to the database is for design
purposes? How can I keep everyone's FE's updated without a great amount of
time.

Thank you

Karen

 

[John Vinson]

I'm still wondering how three unrelated databases would go down within a few
days, the FE/BE seems to be an issue which might of prevented it from
happening but not the cause. any thoughts?

Check your network. Somebody may have a bad NIC (Network Interface
Connector); Access is exquisitely sensitive to network noise. The NIC
might have gone bad and the user whose machine it's in touched the
victim databases over the next few days.

Or, you might have a rogue user who does not realize that ctrl-alt-del
and Stop Process on a slow-running query is about equivalent to
turning off the prop on an airplane flying at 500 feet in bad weather.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Nikos Yannacopoulos]

Karen,

See my answers following your specific questions.

Regards,
Nikos

Thanks Nikos,

I'm still wondering how three unrelated databases would go down within a few
days, the FE/BE seems to be an issue which might of prevented it from
happening but not the cause. any thoughts?

See John Vinson's reply on this; the network connection problem would
definitely explain the timing.

Some of my multi-user databases have nothing but tables in them. Would a FE
still help prevent corruption?

I've never had a similar case, but if I were to put my money on an
answer, that would be that as long as several users are opening the same
database directly you're in for corruption, regardless of whether it's
just tables or other objects as well.

Some of my users are the people building queries, forms and reports (Usually
different people working at different times). How do you deal with FE's,
when the very process the people need access to the database is for design
purposes? How can I keep everyone's FE's updated without a great amount of
time.

To begin with, you should check out Tony Toews's Auto FE utility, which
distributes updated FE's to users automatically:

http://www.granite.ab.ca/access/autofe.htm

Now, to your multiple developers: again, I've never had a similar
situation, but amalgamating my experience with ERP's I would suggest a
scheme which involves:
* a test FE for each developer
* a master test FE
* a master production FE
* an administrator (yourself?)
The idea is that each developer adds objects in their test FE, when done
they notify the administrator who imports the new objects in the master
test FE where he/she (and, possibly, other developers) can test them to
make sure they work as expected and do not create conflicts with other
objects, and once the test results are satisfactory, the new object are
imported into the master production FE to be distributed to all users.
Before embarking on a new development, a developer should take a copy of
the master production FE to use as his/her test FE for the new development.
I realize this may look complicated at first, but it's not too hard to
implement and it pays off.