How to Compact a Database through VBA

This is one of these questions that we see on a regular basis in various Access forums, so I thought I’d try to cover the subject today.

The Larger Issue

Whenever this question is brought up, it raises a much broader discussion as to why one needs to compact a database in the first place.

A well designed database typically does not require compacting, certainly not frequently.  Database bloating (size increase that can be recuperated by a compact) usually indicates a database design issue.  So normally I would review the structure and workings of the database itself to try and find the underlying issue(s) to address.

Some of the Common Causes of Database Bloating

There are a number of possible causes of database bloating, but here are a few:

• Using Record-Level Locking (under client setting in the options dialog)

“Access does not have true row locking, but does have what is called database page locking. So in a roundabout way, if you turn on row locking, then Access just expands all records to the size of one database page – the result is massive bloating.” – as explained by Albert Kallal

• Adding and deleting large quantities of records
• Use of temporary tables
• Sharing a common database front-end file amongst multiple users

What is the Cause for Concern Over Bloating?

The obvious answer here is performance.

I have personally seen databases 1.4GB in size be reduced to 35MB after a compact.  Imagine 20+ users copying a 1.4GB file over a network multiple times throughout the day,  Access having to juggle such a huge file, the impact that could have on the network itself, …

Breaking the 2GB file size limit.  By having processes that cause bloating it is possible that a file that is well within the 2GB file size limit could easily grow and exceed the limit and cause the database to fail entirely.

Typical Approaches to Compacting Databases

Before delving into VBA approaches, I thought I’d elaborate the 2 most common approaches to handling this that are employed by most experienced Access developers, which does not actually employ VBA in any manner.

The Front-End

Commonly, the easiest solution to bloating and other issues related to the front-end is to simply serve up a fresh copy of the front-end every time the database is launched.  By using something as simple as a VBScript to launch the database, you can copy over a fresh copy and then open it at the click of a button.  This guarantees a pristine copy for every use.

The Back-End

Compacting the back-end is a little trickier by its nature of having multiple simultaneous users connected and locking the file from being compacted.  As such, what you will typically see done is setup a Scheduled Task (which calls a simple bat file) to perform the Compact process during late hours when no one will be in the database.

What About Using Compact on Close?

Typically, Compact on Close should be avoided.

Compacting a database is a complex operation.  Although rare, compacting a database can actually cause corruption within a database.  This is yet one more reason why backups are so important, as is, not compacting routinely without reason.

By Compacting you can also incur a performance hit.  You see, when you use Access, it does various things behinds the scene and creates a ‘working space’ that it uses subsequently.  Every time you compact the database, this ‘working space’ is reset so the next time you run the database it must be recreated.

How does Compact on Close Work on a Back-End File?

Setting the Compact on Close property on in a database back-end will cause it to compact when the last user exits the database.

Back to the Question at Hand!

Enough said already, so how can one do this using VBA regardless of all of the above?

The Front-End

Here we will use a little trick:

• Set the Compact on Close option On
• Close the database, so it compacts
• Restart the database
• Set the Compact on Close option Off, so it doesn’t run subsequently for no reason

The Back-End

Things are much trickier here.

With the assumption that no one else is currently using the database (so the file isn’t locked):

• Close all opened objects to ensure we don’t have a connection locking the database
• Get a listing of back-end files
• Compact each file, one by one using DBEngine.CompactDatabase

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Download a Demo Database

The following demo includes both approaches: front-end and back-end compacting.  Furthermore, although coded on Access x32, no APIs are used, so these technique should be fully transferable to Access x64 as well.

Feel free to download a 100% unlocked demo copy by using the link provided below:

Version History