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

    Tip: Compact and repair problems (Access 2003 SP3)

    The Compact and Repair option in Tools | Database utilities works as follows:

    1) Create a compacted version of the database and names it db1.mdb or similar (if there is already a database named db1.mdb, the new one will be db2.mdb, etc.)
    2) Close the current database.
    3) Delete the current database from disk.
    4) Rename the compacted version to the name of the original database.
    5) Open the database.

    After installing Access 2003 SP3, compacting a database to itself requires that you have Full Control permissions on the folder in which the database resided. In SP2 and before, it was sufficient to have Modify permissions.

    In general, this will not be a problem with databases on a local hard disk, but it could be a problem if you're working with a database on a network drive - network admins often give users Modify, not Full Control permissions on network folders.

    If you don't have Full Control permissions, Access will create a compacted version db1.mdb (step 1) and leave it at that, so you end up with two databases: the original, uncompacted one and a new compacted one. If you have set the database to Compact on Close, you'll get a new copy each time you close the database. This can quickly lead to a proliferation of databases named db1.mdb, db2.mdb etc.

    If you experience this problem, you could ask your network admin to give you Full Control permissions on the folder(s) containing Access databases. If this is not possible (which is likely, your network admin might well see it as a security risk), avoid Compact on Close for databases in a network folder.

    If you want to compact a database in a network folder, you'll have to close the database in Access, then perform steps 2 to 5 manually.
    Alternatively, copy the database to a local hard disk if you want to compact it.

    Note that these workarounds will only be necessary if you have Access 2003 SP3 and if you don't have Full Control permissions on the folder containing the database.

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

    Re: Tip: Compact and repair problems (Access 2003 SP3)

    Update: this problem appears to have been solved along with several others in the Access 2003 post-Service Pack 3 hotfix package dated December 18, 2007. This hotfix was mentioned by Mark Liquorman in <post:=685,504>post 685,504</post:>.

Posting Permissions

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