To Compact on Close or Not
If you use temporary tables for various processing needs, with methods such as Make-table queries, imports or basic SQL insert statements, chances are you’ll have some significant Access file bloat over time. Other than filling up disk space (if that’s a concern) the other issues are that bloat can begin to slow down an Access application and perhaps create the potential for corruption if the bloat gets to a critical level.
The need for temporary tables can be satisfied by using a separate database with these types of tables in it and then just using table links from your main application to the separate database and it’s temporary tables. That leaves the bloat outside of your main database application and solely in the separate database. If you have any issues with the separate database it can be compacted, recreated or restored without concern over data loss given the temporary nature of the data.
Routinely compacting a database (using the Compact on Close setting) that doesn’t need it can actually slow down the database somewhat. Probably not a lot, but some amount. 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 by design. 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.
There is also a chance of database corruption any time a compaction is performed. This risk is less nowadays with more reliable networks and hardware but the chance still exists. Even with a single-user database, if it’s very large and on a network, it has a chance of some network glitch causing the whole compact/repair to fail. A failure during a compact means the database is likely lost. There is no recovery.
If you can avoid the auto-Compact on Close and make compacting a deliberate action, you’ll be at far less risk. When compacting a large database on the network, a good strategy is to copy the database file to a local drive, compact it locally, then upload it back to the network. That way you’ll have a backup in case anything goes wrong.