Should I use the Compact On Close feature of Access?

I generally try to dissuade people from using the Compacting on Close feature of Microsoft Access, especially in a multi-user environment. Why?

Well, there are a variety of problems.

First of all, if your database bloats so quickly that it needs to be compacted every time it closes, then you have a design issue that should be addressed. See my blog post: How can I How Can I Compact my Access Database Less Often?

The two major causes of bloat are 1) importing data to a temporary table for processing and 2) creating temporary, intermediary tables with Make-table queries or other methods.

The data import problem can be addressed by linking a table or file for processing rather than importing it. If it can't be linked, it can be imported into a second, temporary database and then linked. This temporary database can be deleted later. I have a sample on my website called ImportToTempDatabase.mdb, which illustrates how to do this.

The problem of intermediate tables can also be solved by use of a temporary, local database which you create at startup and delete on database close. The solution is similar to the data import solution.

Secondly, compacting a database that doesn't need it will actually slow down the database some.  Not a lot, perhaps, but some.  Every database needs working space that Access has to create.  If your database grows a lot on first opening. but then very little on subsequent uses, this isn't bloat. It's the normal working space that your database needs.  Removing that working space every time you close the database requires Access to create it again the next time you use it.

Thirdly, it may be useless. A majority of Access databases are (or should be) in a Front-End/Back-End (FE/BE) configuration. This is often called a "split" database, where the tables reside in a separate database from the one which holds the queries, forms, reports, and code. Compacting on Close will only effect the database actually being used, which in most cases is going to be the FE. Unfortunately, it is the BE where the bloat actually occurs, so at best, compacting on close is useless, at worst, it's actively dangerous. Which leads me to the next point.

Fourth, and most importantly, there is a chance of database corruption any time you compact the database.  Admittedly, with more reliable networking hardware and software, this chance is smaller than in years past, but it's still there.   A small database on your local drive probably has little chance of corruption.  However, even a single-user database if it's very large and on a network has a chance of some network glitch causing the whole compact/repair to fail.  A failure during a compact means the database is toast.  There is no recovery.

A large, multi-user database on a network has a much greater chance of failure. 

So, I prefer to do a compact as a deliberate action rather than a hidden default.  Often, when compacting a large database on the network, I will copy it to my local drive, compact it locally, then upload it back to the network.  This gives me a backup in case anything goes wrong.

Also, if you compact the database programmatically (I have samples on my website: CompactDatabase.mdb and CompactBackEndDB.mdb), you have control to make a backup if you wish.