Nightly maintenance design pattern

[friendlyfriend]

I've implemented the following with an Access 97 application and I'm wondering if it is a common design pattern and whether it has a better implementation or if it is obviated by newer versions of Access. If not, I'm wondering how I could best share it with others.




Solved Problems:
1) Database becomes corrupt unpredictably
2) Performance slows as the database gets bigger


This is how it goes...


Nightly maintenance process scheduled and run as a Windows Task on the server:
1) check that everyone has exited the system. set a flag and use a listener in the application. provide a warning that lets active users prevent the maintenance if necessary. record the user name of who prevented it. otherwise, force any open apps to close.
2) copy the database in case there's a problem with the maintenance. save 4 days of copies and delete the oldest every day before the next maintenance
3) move old records from the main database to an "archive" database. this is entirely app specific.
4) compact the main database and archive databases
5) log the maintenance success/failure
(6. I haven't done this, but would like it to send me an email whenever there's a failure.)




Then as people use the application, whenever they need to access an archived record in a report or form or anything, the application moves the record back from the archive to the main database. Later that night in the next maintenance run, the record is moved back to the archive along with the other newly old records.

I'd appreciate your thoughts.

[June7]

I don't know anything about newer versions that would better accommodate this process.

Your db is large enough to require archiving records?

[friendlyfriend]

I don't know anything about newer versions that would better accommodate this process.

Your db is large enough to require archiving records?

The active database is about 115 MB. The archive is about 215 MB. When we first started doing it, the single database was around 150 MB. Archiving has sped up certain report times to about a tenth of the time it took before.

Switching to a database server with stored procedures may have worked too, but this just seemed to open fewer cans of worms.