Compact on close (2003 sp2)

Topic

New Reply

Is it safe to set a shared database to compact on close?

Reply | Quote

Replies

Access will only try to compact the database when the last user exits the database. It can go wrong if users frequently open and close the database: if the last user closes the database and another user opens it immediately afterwards, Access may not be able to compact or to rename the temporary database it creates while compacting. Therefore I don’t set backend databases to compact on close, but I do frequently set frontends to do so (each user has an individual copy of the frontend).

Reply | Quote

Although the database is shared, it is not split. I have no say over it, I just get a call to compact it now and then. Each user is in the habit of opening it and leaving it open all day. I knew Access would know if more than one user had it open, but I did not know if Access could prevent it being opened while being compacted. I take it, it cannot.

Reply | Quote

You should urge those who do have a say in it that the database should be split. You can point them to WendellB’s Why Split a Database?

When a database MyDatabase.mdb is compacted, the following happens:
1) Access creates a compacted copy of MyDatabase.mdb under a generic name such as db1.mdb or db2.mdb.
2) It closes MyDatabase.mdb.
3) It deletes MyDatabase.mdb.
4) It renames the compacted database to MyDatabase.mdb.

If a user reopens MyDatabase.mdb between step 1 and step 3, Access cannot delete it, so you end up with two copies of the database.

Reply | Quote

I just want to add my voice to the chorus to urge you to STRONGLY suggest to the powers that be that the database should be split. More than that, each user should have their own copy of the frontend on their local machine. More than anything else, a shared “all-in-one” database is very susceptible to corruption and to bloating.

Reply | Quote

Gentlemen, thanks for your input.

Reply | Quote