Any opinions regarding the use of Compact On Close

[bob fitz]

I seem to remember reading some years ago that the use of "Compact on Close" should be avoided because it could cause a file to become corrupted.
Is this still the case or is my memory failing me, once again?

 

[Cotswold]

As I understand it, if one user compacts an .ACCDB there should definitely not be any other users still with the same file open.
Personally I wouldn't do it. I prefer to C&R when I think it is necessary. Which is usually not that often. If you compact on close, you may end up doing it 20 times a day, is that really necessary?

 

[Uncle Gizmo]

I use compact on close whilst developing. I have never had any issues with it to date.

 

[isladogs]

The problems with compact on close are that if anything interrupts the process that can leave your project in an unusable state.
Problems are most likely when the database being compacted is very large and if the compacting takes a long time.
I see no benefit in using compact on close even during development and would definitely never use it in an application distributed to clients

 

[The_Doc_Man]

Further, you have to remember that if you have split the DB, a C&R only affects the FE.

For FE C&R in a "split" situation, you would do better to just use the auto-uploader to get a fresh (and therefore, presumably saved in a C&R state) copy of the FE. That solution is more like a C&R on Open than on Close but gives you a decent result.

For a monolithic, non-shared DB, I would STILL be a bit hesitant. In that case I would make a copy first, then C&R the copy. If THAT appears to work, then replace the production copy with the freshly C&R'd copy and save the original as a backup.

If you have a split DB and kick everyone out of the BE, then the same rule would apply as for the monolithic (single-file) DB. Backup, then C&R the copy, then if it looks OK, do the replacements.

 

[Pat Hartman]

I also prefer to control when a C&R happens so don't set it on autopilot. I never never ran into a problem in the dark ages when I did have C&R set to run on close. One thing to be conscious of though is to NEVER, compact a db unless it is on your local drive. Apparently the C&R process is quite I/O intensive and it can take a long time to do over a LAN and the longer it takes, the more susceptible it is to corruption. If you have to compact a db that is on the server. Rename the server version to avoid anyone opening it while you are working with it. Download it to your PC and then put it back. Rename it back to the original name. This will minimize the risk and if the db has any size to it, it will also be faster.

For the users, I use a batch file that loads a copy of the clean FE each time they run their shortcut. This takes almost no time and it eliminates the need to ever compact the FE once it is deployed.