CODEWITHSUNDEEP
vbams-access
Absinthe
Absinthe

Reputation: 3391

Implications of overwriting a corrupted database file

We have an Access database split front-end / back end that frequently corrupts (for various reasons; bad architecture, bad code, too many users, slow network and so on, we're currently re-writing in SQL Server). Usually when this happens admins email all staff and ask them to exit the front end and any other files that link to the back end (e.g some reports in Excel have connections to it) so we can open the DB and have it auto-compact / repair when it detects its in a corrupted state.

Getting users out of the system is like herding cats and we're not always able to do it in good time. I've implemented a form timer event that checks a 3rd DB for a flag as to whether it should remain open, the idea is we set that flag to false when we need the front ends closed. This seems to be effective but I can't say for sure if it works on 100% of installs as sometimes we still experience that file is locked. This may be because of the Excel reports though these are viewed rarely.

Lately, rather than waiting for people to exit I've been making a copy of the corrupted DB before opening it, repairing the copy and then overwriting the original with the copied file when the repair is finished. This seems to work well.

My question is: What are the issues, if any, around overwriting the backend? Could it cause any problems that aren't immediately apparent? I've been doing this a few weeks now and haven't noticed any issues but it just feels like a bad practice. e.g. What happens to the lock file? Does that get updated automatically?

Upvotes: 1

Views: 80

Answers (1)

Erik A
Erik A

Reputation: 32642

Not much, because the worst case already happened.

When copying an open Access database, there's a risk of open transactions and writes being half-finished, corrupting the database, not being committed, or trashing the VB Project part of the database.

But, the file is already corrupted, and when closing it if you have an open transaction, you will receive an error message (that's also a plausible cause for why your form timer isn't working).

I don't have statistics, but I think closing up a corrupt database by writing transactions to it is likely more dangerous than just copying it with open transactions, as these writes might overwrite stuff they shouldn't.

Of course, never do this when your database is not corrupted, since it can cause corruption if the database is not already corrupted.

Of course, if you have intermittent corruption, then the real issue should be preventing that from occurring, and the bug Gord Thompson referred to in a comment (this one) is very common and likely the culprit. It can go good 20 times in a row, until it goes wrong once and you'll have to revert to a backup, possibly losing data (or worse, not having a backup and losing much more data).

Upvotes: 2

Related Questions