How Can I Compact my Access Database Less Often?

So far in this series, I have talked about what compacting is, how it works, and several ways to compact, both through the Access Interface and in VBA code.  However, one subject that's rarely discussed is how to reduce the need to compact the database in the first place.  The number one reason for compacting is due to "database bloat", where records are added, deleted, and the space is not recovered until compacted.  Reducing bloat will reduce the frequency of having to compact your database.

Here are some strategies:

Split the Database (FE/BE)
A split database is one in which the application (queries, forms, reports, macros, and code) are in a separate physical database file (known as the Front-End or FE) from the tables (which file is known as the Back-End or BE).  Since bloating affects the tables most, having them in their own file means that only the BE needs to be compacted.  As I showed last time, this can be initiated from the FE fairly easily.

There are other good reasons besides this to split your database, including improved multi-user access and less corruption.  Splitting the database is such a good idea, in fact, that I recommend it for almost every application.

Importing Data
Just as bloat is the number one reason for compacting, importing data is the number one reason for bloat. A common scenario is importing at text file to a temporary table, massaging or formatting the data in some way, appending it to the permanent table, then deleting the temp table.  As we've seen, while Access with grow the database dynamically, it will not automatically shrink it when the data is deleted.  If you import a lot of data this way, you can find yourself needing to compact often.  There are a couple of ways around this:

Linking Files
Instead of importing files to a temporary table, consider linking them.  You can link many kinds of files including external Access tables, Excel files, text files, or other external sources.  These linked tables do not contribute to bloat nearly as much as importing them, although as we'll see later, processing them may cause some database growth.  Of course any time you add records to your database, the file size will grow.  Nothing can be done about that.  But minimizing the amount of temporary data you import to your database will keep growth within normal bounds.

Importing to temp database
If you must import data to a table for some reason, consider creating a temporary database to store the data and link those tables into your database.  After you're done with the import process, the temporary database can be deleted. You an even create, populate, link, process, and delete temporary databases programmatically from your FE application.  On my website is a small sample database called ImportToTempDatabase.mdb which illustrates how.

Don't obsess - some "bloat" is okay
Not all bloat is bad.  Some of it is necessary.  Aside from inserting and deleting records, databases can grow due to internal processing.  When the database engine runs a query or a recordset based on a query, it has to process and store it somewhere, and that "somewhere" can cause your database to grow.  This growth is not a cause for concern.

If you find that immediately after compacting your database grows dynamically at first, but then slows to minimal growth, that first, dramatic growth is just Access creating the working space it needs.

.