What causes bloating of .mdb files?

Posted on 2009-04-22
Last Modified: 2013-11-29
I have an Access2003 database with about a dozen tables, at least one of them some 250,000 rows of fields and several other tables with 3,000 rows.  Each time I use the database I notice that the size seems to gradually increase.  When I run "compact and repair" the difference in size is dramatic (from 300,000KB to 70,000).  I'm certain that it runs faster at the smaller size.

What is likely causing this bloat?  Do you recommend that I encode "compact and repair" into the code for my "Exit Application" button?  I need some experienced advice.

Question by:Greg-C
    LVL 16

    Expert Comment

    by:Chuck Wood
    Every time a record is deleted, it leaves space in the database. You have three choices on this issue: set your DB to compact on close, regularly (daily, weekly, etc.) run a compact and repair during the hours no one is using the DB, or schedule a compact and repair during the off hours.
    LVL 16

    Accepted Solution

    Here is an example of a line of a .CMD file that I schedule to run at 1:00 AM every day to compact the database.

    "C:\Program Files\Microsoft Office 2000\Office\jetcomp.exe" -src:"PathToSourceDb" -dest:"PathToDestDb"

    Open in new window


    Author Closing Comment

    I appreciate your insight.  I'll set up some sort of schedule and have the system remind the client when a compact is due (perhaps "time since last".  Looks good.  Thanks!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 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

    23 Experts available now in Live!

    Get 1:1 Help Now