Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    43

    Unanswered: opinions on compact on close

    greetings, all:

    i am writing a metadata report regarding an Access 2000 database, a database i inherited, and one thing i have observed is that compact on close was not selected. over the months, i have used the database and selected compact on close, and there have been multiple instances where compacting on close did not successfully finish and the database became corrupted. yes, i have backups...

    here is my question: i'm curious as to whether or not there is any reason why compact on close should not be selected. whenever i have made DBs from scratch, i've pretty much made this de facto, but in this case, until i'm done reporting my findings for the next step, i've chosen not to select this option on this db.

    just a little background on this db, btw:

    • there is a lot of vba that automates processes.
    • there are many append, delete, and update queries that work on the same table(s).


    i googled my question and actually read some pages that say that compact on close is stupid, so i'd really love to hear any DBAs' viewpoints here.
    Mos

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    While coding/working on the mdb, I always compact and repair it as part of my coding process but I don't have it done automatically when the users get into it since this can be time consuming (few seconds) and most users don't want to wait the few seconds to do it when opening/closing the mdb. Since I use SQL Server linked tables mostly now, it really isn't much of a factor.

    I will often compact and repair a few times. For the few seconds it takes to do it, it doesn't make sense not to do it. I'll get an error every now and then where it doesn't successfully compact and repair. I'm not really concerned if it doesn't as I simply redo it and it then completes successfully.

    I'll get a little concerned if I can't successfully compact and repair after a few times.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2005
    Posts
    43
    thank you for the quick response, Paul. if i may pick your brain again, you mentioned being "concerned" if it doesn't successfully C/C after a few times. what is your "plan of action" if that is the case? this has happened with my db.
    Mos

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If it doesn't compact and repair successfully after several tries, I then look at the option of importing all the objects into a fresh mdb. If it still doesn't compact/repair properly, I then start looking at the drive/disk space. I'm always hesistant to compact and repair on network drives verses my c: drive. If I still have problems, I start diving into the code to find out the cause. After that, I look at the tables and fields and data looking for "bad" data values, even starting with blank tables.

    Finally, if it's still a problem, I start picking it apart, importing just the tables into a new mdb, then the forms, reports, modules. It's not a fun process and sometimes I can't find the cause, but I can usually get to the point where I have a good working mdb which compacts and repairs without problems.
    Last edited by pkstormy; 12-10-07 at 22:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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