Bloating Access 2007 accdb file

Posted on 2007-07-26
Last Modified: 2013-11-28

We have a Access database we use for logging tasks in our schedule (developed internally but not by me - I'm networking & OS not a DB engineer).

This was first written under 2003 and we have recently had to make certain changes so it will work under 2007.

It seems to work fine but we are experiencing a 'bloating' issue. The DB is about 20-25Mb after a compact & repair but if left to its own devices it can grow significantly in just a few days (to the order of a half gigabyte!)

I am aware of "Compact on close" but the DB will usually be open by a minimum of 10-15 users at any one time, sometimes as many as 50 and i'd have to kick each and every one of them out to do this (and ensure that other don't open it in the meantime!)

Can anyone provide any ideas as to what may be causing this? Is it a potential bug in 2007, do we have a bug in the code of the DB etc etc

Thanks all!

Question by:bheroniphr
    LVL 92

    Accepted Solution

    Hello bheroniphr,

    1) Make sure that you are using a split architecture for this app.  In split Access apps,
    it is typical to have 1 "back-end" file as the permanent data store, and then each
    user gets his/her own "front-end" MDB or MDE file with the forms, queries, reports,
    and code.  If your reports require creating "temp tables" (sometimes useful to
    facilitate complex reporting), put those temp tables in the front-ends, or in a separate
    "temp MDB" file.

    2) Do set the back-end to compact on close.  The compact/repair should occur when
    the last user is out.

    3) If the app has a lot of INSERT and DELETE operations, the file size can grow
    enormously but then shrink back down on a compact.  Just part and parcel of how
    Jet manages disk space.  Similar things can happen on other db platforms.


    LVL 6

    Assisted Solution

    If you have a time when you know that there will be no users in the database, you can schedule a task to do the compaction.

    Also the split architecture is a great idea. If the DB is not too complex you may want to think about using SQL server for your back-end storage. There is an upgrade wizard in Access for SQL server, but be warned that in my experience it is not a one-click experience for all but the most simple DB.

    As matthewspatrick said, inserts and deletes in Access cause this bloat and there's no way (that I know of) to stop that behavior in and MDB.

    LVL 77

    Assisted Solution

    '2) Do set the back-end to compact on close.  The compact/repair should occur when
    the last user is out.'

    Sorry Matthew, I don't think this is so.
    The backend is never open in Access UI terms, and the compact on close is a UI feature not a Jet feature, as I understand it.  
    You would have to issue a compact command from the frontend but you have to take account of the fact that there might still be other users connected and this would cause the compact to fail.
    And you would need to sort out the fle names after the compact.

    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    Are you storing any images?
    If so >>>  DBPix ... will fix the bloat.

    "2) Do set the back-end to compact on close.  The compact/repair should occur when
    the last user is out."
    If it's already split,  that would happen only if the BE is opened and closed manually.


    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    See these links I am accumulating for A2007 - a good reference in general:  'Transisitioning to A2007    ' Various A2007 Issues    'A2007 Limitations list   ' A bunch of good stuff    'Ribbon
    LVL 92

    Expert Comment

    by:Patrick Matthews
    DatabaseMX said (and Pete said something similar):
    >>If it's already split,  that would happen only if the BE is opened and closed manually.

    For some reason I thought it was happening as I described.  Thanks for that bit of
    enlightenment :)
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    760 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

    15 Experts available now in Live!

    Get 1:1 Help Now