MDB file disappearing

[dave755]

My MDB file is being deleted somehow.

I have an installation in which an Access DB is being used as a temporary data collection repository. As the data is transferred from the collection point to the primary server, lots of records are being deleted.

I discovered that, over several time, this behavior will eventually cause the MDB file to become corrupted. To counter this problem, I have set up a shortcut in the Startup group that does an automatic "compact and repair" on the MDB file every time the computer is booted.

The machine is still in the shop, so it is very unlikely that a person is deleting the file.

While the machine is being worked on, the computer is being rebooted frequently - mostly as a result of power cycles.

When the machine is powered off, Win2000 is not usually shut down properly. They just throw the big switch.

What could cause this behavior?

Dave Gee

 

[cheerio]

Compact and repair works by copying the contents of the mdb file object by object into a new blank database that is given a temporary name. At the very end of this process the old database is dropped and the new one is renamed to take on the original name. Unless you were to power off at the very moment that the temporary mdb is being renamed all that would happen is you would have the original mdb and a partly filled temporary mdb.

If people are going to power down while Access is in use you may be wise to think again about your approach. I would be temped to have the data in one mdb and use unbound forms in a separate front end mdb acting only as a user interface which should contain only static objects such as forms and queries.. At the moment the user adds a new record I would open the data mdb in code, update it and close that mdb again. In effect the data would only be vulnerable to loss through power down immediately after the user has pressed a submit button.

Also if you have split mdb files I would be tempted to recreate the backend database immediately after its data has been uploaded using Data Definition Language rather than trying to clean out lots of hidden deleted records with Compact/Repair.