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

WHAT ABOUT THE BE (BACK END)??????


Regards

Chris
LVL 2
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
 
PatHartmanCommented:
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.
0
 
PatHartmanCommented:
You might want to take a look at a tool offered by FMSINC.com  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.
0
 
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...
:-O

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

JeffCoachman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
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.
0
 
Gustav BrockCIOCommented:
And in most cases you don't even have to care about compacting the backend ....

/gustav
0
 
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you
0
 
PatHartmanCommented:
Member_2_7948725,
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.