Automatic Compact and Repair of MS Access Database

Question

We have an MS Access based system that relies on back-end data stored in networked MS Access database. Sometimes those databases get corrupted when random networking issues occur. So we thought we should add some sort of process that can routinely fire off a compact and repair.

The problem is that this database is used all the time by multiple users, and there is no guarantee to know that a certain time will be available when the database is not in use. So my question(s) are as follows.

1) If the database is being c/r'ed when a front-end app (MS Access or .NET) tries to connect to it, what will happen? Is that ok?

2) What does SO recommend as a good solution to make the front-end apps all wait until the backend MS Access database is ready for use? The fewer changes needed to the front-end apps the better.

Thank you!

Answer 1

This is a relatively frequently-asked question among Access developers.

There are two parts to it:

1. the code to backup and compact the database, which you'd schedule on the server.

2. code that you put in your front end to automatically log everybody out at a time that allows the code in #1 to run.

Generally, an office is only open from, say, 8am to 6pm local time, so it's generally safe to kick everybody out around midnight so that the back end is available for backup and compacting at, say, 1am, in preparation for your regular file server backup which might happen, say, at 2am.

A VBScript for the backup/compact is relatively easy to write. If you need help with that, ask.

For the front end, you'll need to have a hidden form that opens when the app is open with a timer on it that's set to kick in at midnight and close out the app. How elaborate you want to get with that depends. Potential pitfalls would be if a user has started a record and it's in a state that can't be saved (fails validation rules for some reason). That can be complicated (you'd have to trap for the error, undo the stump record and then you could shut down), but it's better to engineer your apps so users can't ever create records that can't be saved.

The key point is that you have to plan both ends of this, the scheduling and shutting down any connections that remain at the time your backup/compact is scheduled to run.

Answer 2

From what I understand, having it automatically compact the database is a bad idea. If there was a recoverable error BEFORE compacting, you might end up with a completely unreadable database afterward - requiring a restore from backups.

1> If the front-end tries to access a database that is being compacted/repaired, I believe that front-end client is told the database is unavailable and is unceremoniously ejected. I believe you could write an error handler for this, but that requires more VBA knowledge than I have to offer.

2> If at all possible, when dealing with multiple clients - a RDBMS on the backend will always perform better, even if it's Microsoft SQL Server Express on a spare Windows box. Data is going to have a better chance of not getting corrupted when 'bad things' happen (network disconnections, client lockups, etc). A RDBMS with transactions is usually a good route for this.

As far as a solution to this, I am afraid I am short of ideas programmatically. Sorry.

Answer 3

Garry Robinson's book about Access Security deals with this issue. It is well worth a read . On Garry's website (vb123.com) he sells a toolbox with a number of admin and security tools included, including one that gives each connected workstation a warning and then it shuts down the front end x seconds later.

The book is:

Real World Microsoft Access Database Protection and Security

If you are working with Access in a corporate environment, it is a must have and read.