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!
This is a relatively frequently-asked question among Access developers.
There are two parts to it:
the code to backup and compact the database, which you'd schedule on the server.
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.