How to compress an MS Access database

Question

I have an .mdb file which is 70MB.

After deleting all records contained in the file, the size remains 70MB.

How do I make my .mdb file smaller?

Answer 1

Open the mdb and do a 'Compact and Repair'. This will reduce the size of the mdb.

You can also set the 'Compact on Close' option to on (off by default).

Here is a link to some additional information: http://www.trcb.com/computers-and-technology/data-recovery/ways-to-compact-and-repair-an-access-database-27384.htm

Answer 2

Every database engine that has ever existed needs regular maintenance operations run on them to optimize data storage and to recover slack space. Back in xBase days, you ran a PACK command to remove deleted rows, for instance. On SQL Server, you run scripts to shrink the actual data files for the same reasons.

Why does every database engine do this?

Because it would be a huge performance hit if every write to the database had to rewrite the whole file in optimized order. Consider a database that stores each data table in a separate file. If a table has 10000 records, and you delete the 5000th record, to get rid of slack space, you'd have to rewrite the whole second half of the data file. Instead, every database uses some form of marking the space used as unused and discardable the next time the optimize operations are run on the data table.

Jet/ACE is no different in this regard than any other database engine and any application using a Jet/ACE database as a data store should have regular maintenance operations scheduled, including a backup and then a compact.

There are some issues with this in Jet/ACE that aren't present in server database engines. Specifically, you can't compact unless all users have closed their connections to the data file. In a server database, the users connect to the database engine's server-side process, and that server-side demon is the only "user" of the actual data files in which the data is stored. Thus, the server demon can decide when to perform the optimization and maintenance routines, since it's entirely in control of when the data files are in use or not.

One common problem with Access applications is that users will leave their application open on their computers and leave the office for the day, which means that when you run your compact operation, say at 2:00am, the file is still open and you can't run it (because compact replaces the original file). Most programmers of Access applications who encounter this problem will either tolerate the occasional failure of this kind of overnight maintenance (volume shadow copy still allows a backup of the file, though there's no guarantee that backup copy will be in a 100% internally consistent state), or they will engineer their Access applications to terminate at a time appropriate to allow overnight maintenance operations. I've done both, myself.

In non-Access applications, the same problem exists, but has to be tackled differently. For web applications, it's something of a problem, but in general, I'd say that any web app that churns the data enough that a compact would be needed is one for which a Jet/ACE data store is wholly inappropriate.

Now, on the subject of COMPACT ON CLOSE:

It should never be used by anyone.

Ever.

It's useless and downright dangerous when it actually kicks in.

It's useless because there's no properly-architected production environment in which users would ever be opening the back end -- if it's an Access app, it should be split, with users only ever opening the front end, and if it's a web app, users won't be interacting directly with the data file. So in both scenarios, nobody is ever going to trigger the COMPACT ON CLOSE, so you've wasted your time turning it on.

Secondly, even if somebody does occasionally trigger it, it's only going to work if that user is the only one with the database open. As I said above, it can't be compacted if there are other users with it open, so this isn't going to work, either -- COMPACT ON CLOSE can only run when the user triggering it has exclusive access.

But worst of all, COMPACT ON CLOSE is dangerous and if it does run can lead to actual data loss. This is because there are certain states an Jet/ACE datebase can be in wherein internal structures are out of whack, but the data is all still accessible. When the compact/repair operation is run in that state, data can potentially be lost. This is an extremely rare condition, but it is a very remote possibility.

The point is that COMPACT ON CLOSE is not conditional, and there is no prompt that asks you if you want to run it. You don't get a chance to do a backup before it runs, so if you have it turned on and it kicks in when your database is in that very rare state, you could lose data that you'd otherwise be able to recover if you did not run the compact operation.

So, in short, nobody with any understanding of Jet/ACE and compacting ever turns on COMPACT ON CLOSE.

For a single user, you can just compact as needed.

For a shared application, some kind of scheduled maintenance script is the best thing, usually running overnight on the file server. That script would make a backup of the file, then run the compact. It's quite a simple script to write in VBScript, and easily scheduled.

Last of all, if your application frequently deletes large numbers of records, in most cases that's an indication of a design error. Records that are added and deleted in regular production use are TEMPORARY DATA and don't belong in your main data file, both logically speaking and pragmatically speaking.

All of my production apps have a temp database as part of the architecture, and all temp tables are stored there. I never bother to compact the temp databases. If for some reason performance bogged down because of bloat within the temp database, I'd just copy a pristine empty copy of the temp database over top of the old one, since none of the data in there is anything other than temporary. This reduces churn and bloat in front end or back end and greatly reduces the frequency of necessary compacts on the back end data file.

On the question of how to compact, there are a number of options:

1. in the Access UI you can compact the currently open database (TOOLS | DATABASE UTILITIES). However, that doesn't allow you to make a backup as part of the process, and it's always a good idea to backup before compacting, just in case something goes wrong.

2. in the Access UI you can compact a database that is not open. This one compacts from an existing file to a new one, so when you're done you have to rename both the original and the newly compacted file (to have the new name). The FILE OPEN dialog that asks you what file to compact from does allow you to rename the file at that point, so you can do it as part of the manual process.

3. in code, you can use the DAO DBEngine.CompactDatabase method to do the job. This is usable from within Access VBA, or from a VBScript, or from any environment where you can use COM. You are responsible in your code for doing the backup and renaming files and so forth.

4. another option in code is JRO (Jet & Replication Objects), but it offers nothing in regard to compact operations that DAO doesn't already have. JRO was created as a separate library to handle Jet-specific features that were not supported in ADO itself, so if you're using ADO as your interface, the MS-recommended library for compacting would be JRO. From within Access, JRO is inappropriate for compact, as you'd already have the CompactDatabase method available, even if you don't have a DAO reference (the DBEngine is always available in Access whether or not you have a DAO reference). In other words, DBEngine.CompactDatabase can be used within Access without either a DAO or ADO reference, where as the JRO CompactDatabase method is only available with a JRO reference (or using late binding). From outside of Access, JRO may be the appropriate library.

Let me stress how important backups are. You won't need it 999 times out of 1000 (or even less often), but when you need it, you'll need it bad! So never compact without making a backup beforehand.

Finally, after any compact, it's a good idea to check the compacted file to see if there's a system table called MSysCompactErrors. This table will list any problems encountered during the compact, if there were any.

That's all I can think of regarding compact for now.