How to compact the back end database?

For the FE we normally use this :

Automatically compact and repair a database when it closes

You can select the Compact on Close database option if you want to automatically compact and repair a database when it closes.

NOTE: Setting this option affects only the database that is currently open. You must set this option separately for each database that you want to automatically compact and repair.

On the File tab, click Options.

In the Access Options dialog box, click Current Database.

Under Application Options, select the Compact on Close check box



Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You might want to take a look at a tool offered by  It allows you to create a schedule to do backups.  You could make your own but ultimately will cost more than this product.  Sometimes its better to build than buy.  You need only one copy of the tool to manage all you databases.
Jeffrey CoachmanMIS LiasonCommented:
I think Pat has your answer here.
(FMS produces some really robust products)

Another way to look at this is:

Since the back end is rarely ever "Opened" (manually), ...there may not be a real need to "compact" it (each time the FE is opened/closed).
The BE is merely being "accessed" by the FE, ...and no BE "design" changes are being made.

So you can still set the "Compact on Close" option for the BE, ...for the times when you do open it manually.

FWIW, ...I rarely turn on the "compact on close" option for any Access db.
I always make a manual backup of any file (FE or BE) before compacting it.

Experience has taught me that, (although rare), ...corruption during Compact, ...can happen.
Resulting sometimes in the infamous "0 byte" database file...

If you set Compact on close, and corruption does occur, ...then by the time you reopen the db, may be to late to recover/fix anything (without a backup).

Dale FyeOwner, Developing Solutions LLCCommented:
Agree that the FMS application is a great tool, but for many of my clients, I just build an option to backup the database on the applications Admin form.

Because this form is unbound, and the application has no active connections to the BE, it might be available to perform compact and repair, if no one else is using it at the time.

When a user elects to backup the database, it checks to see if the .laccdb file is open for the BE database.  If so, then the backup fails and displays a message that it is not available because another user is accessing the database.

If the .laccdb file is not open, then I set a flag (InMaint) in tbl_AppProperties in the BE, which will prevent other users from opening the application.

I then use the dbengine.CompactDatabase method to compact the BE to some location you have selected, with a date time value embedded in the file name.

Finally, I unset the InMaint flag in the BE so that other users can log into the application.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gustav BrockCIOCommented:
And in most cases you don't even have to care about compacting the backend ....

The BE should NEVER be set to compact on close.  The BE is opened and closed hundreds of times each day in a normal application with a few users.  In busier systems with more users, the BE might stay open for long stretches if there are multiple concurrent users.  As long as at least one user is actively retrieving data, the .laccdb stays there.  When the last user finishes, the .laccdb is deleted and recreated when the next user accesses the BE.

I  totally disagree with the advice that the BE doesn't need regular compacting.  As part of the compact process, Access reads, sorts, and writes all tables into PK order and rebuilds all indexes.  The upshot of this is that all table statistics are updated.  Having updated statistics means that when the FE creates execution plans for each query, it uses the statistics to determine the most efficient way to retrieve data.  A common complaint of people moving from test to production is that the app is a dog.  Well that may be that when you were testing, all the tables were small and so the query engine decided to do full table scans rather than utilize indexes and now that the app is fully loaded with lots of data, none of the indexes are being used because no one bothered to update the statistics and recompile the FE to take advantage of that.

When I release a new copy of the FE, i compact it first so that everything is uncompiled and then I compile it before distributing.  Because of the compact, all the queries are in an "uncompiled" state so the first time they execute, Access has to rebuild their execution plan and for that it needs accurate statistics.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you
I can't speak for the others but I grew up in an era where we actually earned our trophies so I prefer to not be awarded participation points.  

When you award everyone participation points even though the advice is conflicting, you make the question useless for people who come across it later because they have no idea which suggestion was actually helpful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.