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

Posted on 2005-04-17
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
    LVL 9

    Accepted Solution

    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:

    Hope that helps...
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    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
    The only downside might be that a large database could take a long time to compact.
    LVL 1

    Assisted Solution

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now