Portland Access User Group
Portland Access User Group Conference September 28-30, 2019
The PAUG Database Designer International conference brings together a wide range of Access developers, consultants, power users and Access enthusiasts. This marks the 21st anniversary of the conference. We will once again be returning to the peaceful and natural surroundings of the Conference Center at Silver Falls State Park, which lends itself to a climate that fosters learning, creativity, and socializing.
Friday, September 24, 2010
How Can I Compact my Access Database Less Often?
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.
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:
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.