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

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.

Gordon
Gordon_AtherleyAsked:
Who is Participating?
 
stengeljCommented:
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...
0
 
Jeffrey CoachmanMIS LiasonCommented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
The only downside might be that a large database could take a long time to compact.
0
 
critter017Commented:
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.  
0
 
Gordon_AtherleyAuthor Commented:
Together, the three answers gave me the strategic advice I needed, so I split the points. Thank you very much.
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.