Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Compact on close (2003 sp2)

    Is it safe to set a shared database to compact on close?
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compact on close (2003 sp2)

    Access will only try to compact the database when the last user exits the database. It can go wrong if users frequently open and close the database: if the last user closes the database and another user opens it immediately afterwards, Access may not be able to compact or to rename the temporary database it creates while compacting. Therefore I don't set backend databases to compact on close, but I do frequently set frontends to do so (each user has an individual copy of the frontend).

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact on close (2003 sp2)

    Although the database is shared, it is not split. I have no say over it, I just get a call to compact it now and then. Each user is in the habit of opening it and leaving it open all day. I knew Access would know if more than one user had it open, but I did not know if Access could prevent it being opened while being compacted. I take it, it cannot.
    Thanks
    chuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compact on close (2003 sp2)

    You should urge those who do have a say in it that the database should be split. You can point them to WendellB's Why Split a Database?

    When a database MyDatabase.mdb is compacted, the following happens:
    1) Access creates a compacted copy of MyDatabase.mdb under a generic name such as db1.mdb or db2.mdb.
    2) It closes MyDatabase.mdb.
    3) It deletes MyDatabase.mdb.
    4) It renames the compacted database to MyDatabase.mdb.

    If a user reopens MyDatabase.mdb between step 1 and step 3, Access cannot delete it, so you end up with two copies of the database.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Compact on close (2003 sp2)

    I just want to add my voice to the chorus to urge you to STRONGLY suggest to the powers that be that the database should be split. More than that, each user should have their own copy of the frontend on their local machine. More than anything else, a shared "all-in-one" database is very susceptible to corruption and to bloating.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact on close (2003 sp2)

    Gentlemen, thanks for your input.
    Thanks
    chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •