[Last Call] Learn how to a build a cloud-first strategyRegister Now


How often is too often to compact a MS Access database?

Posted on 2005-04-17
Medium Priority
Last Modified: 2006-11-18
Experts, please:

My app creates MS Access databases (.mdb files), which the user populates. An implementation of the app could have as many as 100 .mdb files.

I've automated the compacting process, which runs smoothly, and supports a progress bar. Now I am wondering how often it should be run. Right now, it's every time the user quits the app. There's little or no inconvenience to the user.

But is there a downside to compacting so often? If so, are there any guides to the optimum frequency for compacting?

Advice gratefully accepted.

Question by:Gordon_Atherley

Accepted Solution

stengelj earned 400 total points
ID: 13803169
In looking at what Microsoft says... all they really say is that compacting you database frequently and routinely is a good thing.  However, 'frequently' is not quantified.  So, I think that you then just need to look at the risks of compacting.

1) You must have enought disk space for the duplicates during the backup
2) You can't do it while the database is open
3) It should not run while the system defrag is running (ideally, defrag should run first)
4) When automatically compacting, if erros are detected they may go unnoticed and eventually make it though an entire backup cycle.

My reference for most of this: http://support.microsoft.com/kb/209769/EN-US/ 

Hope that helps...
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 total points
ID: 13803358
In addition to what  stengelj says above,

Every once in a while I read that you should always Backup the database before compacting

Whenever you read about corrupt .mdb files, the advice is "Make a Backup first"

This might tie in with Step 4 from the above post.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 13803369
The only downside might be that a large database could take a long time to compact.

Assisted Solution

critter017 earned 400 total points
ID: 13806850
boag2000 comment should not be ignored.  If you do not currently include code for a backup of the database before compacting, you should do that now.  Having experienced errors many times during the compact routine, I cannot stress enough the need to always backup before compacting.  I do not believe the number of times you compact is the problem.  Sometimes Access just seems to corrupt a database during the compact routine.  

Author Comment

ID: 13807478
Together, the three answers gave me the strategic advice I needed, so I split the points. Thank you very much.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question