Compact on close

[RussG]

Hi - I have read conflicting advice on this so wondered what members of the forum would recommend. I have a multi user DB , no more than half a dozen users. DB will be FE/BE and I have set the DB to compact on close. Obviously if another user has the db open this process won't complete. Are there any issues with this, possible corruption?

Thanks
RussG

 

[boblarson]

Russ:

The frontends should all reside on each users computer and therefore it would not conflict with anyone. Do not go with a shared frontend as it makes making updates a pain and it greatly increases your potential for corruption.

As for the backend, you will need to do some maintenance on it about once a week to compact it. There is code out there for compacting the backend too, but all users will need to be out of their frontends to do it.

 

[RussG]

Thanks Bob, that's very helpful.
RussG

 

[dfenton]

I just replied on the issue of "compact on close" in another forum.

Short summary:

NEVER USE COMPACT ON CLOSE.

http://www.access-programmers.co.uk/forums/showpost.php?p=700584&postcount=12

 

[RussG]

Thanks for your message David , this leads me back to my comment in the original post about conflicting advice on this issue.

RussG

 

[burrcm]

It is bound to fail/corrupt eventually, so best not to use it. Always safer to copy then compact. You can do this with a scheduled task. If it is the BE you have set to compact on close however, this will only occur if you open it. Having a linked FE open and close will not trigger it. If you are doing it to the FE's it doesn't matter too much, provided you have a master copy available for the users to replace theirs with. FE without tables won't (shouldn't) bloat much though. Even then, a scheduled task to replace their copies with the master copy would be more reliable.

Chris B

 

[dsfcom]

Good discussion...

I've used compact on close for several years with no problems. The only complaints I've received are that it takes a bit longer to close the db (obviously) and if the user tries to open the db back up straight away and the compact hasn't completed yet, they get a message stating that the db is in use and to try again. Other than that, no lost data or corruption has occured. I do believe, however, that the copy method may be faster depending on where the FE is being copied from. But as far as safer, I'd have to differ.