Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compacting an Access 2002 DB (XP / SP3)

    I have always compacted databases that use temporary tables - either manually or, more often, on close. Lately, no matter how I try to compact it (and this happens with several databases, not just one), I get the message that Access cannot compact the database because the file is in use. I have even tried to compact it to another name with the same result. So my databases are growing . . . . . . These databases do not have attached databases (front end - back end). They contain all data, forms, reports etc in the 1 database.

    Does anyone have any ideas on what I might have done to invoke this behaviour?

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Compacting an Access 2002 DB (XP / SP3)

    One possibility is that there is an LDB file associated with the database that is not being deleted for some reason. The other possibility is that the database is corrupt in some way - to test that theory, import all of the objects into a new empty database, and then see if you can compact it. (You should be able to.)
    Wendell

  3. #3
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting an Access 2002 DB (XP / SP3)

    Thanks Wendell. Worked like a charm! It sure helps to ask questions of people who know the answers.

    Appreciate your help.

    Graeme.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Compacting an Access 2002 DB (XP / SP3)

    You should really split your database into a FE / BE for data security. The problem is that if the database corrupts you could lose the lot. FE databases are most likely to corrupt.

  5. #5
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting an Access 2002 DB (XP / SP3)

    Yes - I do this to most databases but the ones I am having the trouble with import data then report on it so if I lost all the data I would just reimport it. And the single DB is easier to hand around to people. But thanks for the suggestion.

    Graeme.

  6. #6
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting an Access 2002 DB (XP / SP3)

    OK. I have solved this one - and the solution was in a most unexpected place. If I open Windows Explorer and double click the database to open it then close (and compact on close it enabled) or just compact it while the explorer window is still open, the compact fails. If I close the explorer window before I compact then it works! Seems that Windows Explorer has the database flagged as being used so the compact operation cannot rename the file. Undocumented feature?

    Thank you all for your suggestions.

    Graeme.

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

    Re: Compacting an Access 2002 DB (XP / SP3)

    That's strange - I often open a database from Windows Explorer and leave the Explorer window open, but Compact on Close works as intended...
    My Windows version is Windows XP SP-2.

  8. #8
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting an Access 2002 DB (XP / SP3)

    I am on the same Windows version. However, my computer is 3 years old and starting to behave like it. I suspect that something in my total Office installation is a bit dodgy but I am at a loss to find it. But I thought that this behaviour was odd. But it works every time - if Explorer is open, the compact fails but if it is closed, the compact succeeds. One of life's little mysteries. I guess I can live with it now that I can reliably compact a dataabse. It was getting a bit rugged when I had a database that had grown to 80 Mb and I could not compact it!

Posting Permissions

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