We have an access application which is split into a front & back end file &
resides on a single PC. 4 other PCs access the app through a peer to peer
connection. We have created the app so that Access is automatically set to
compact & repair whenever the app is closed on any of the machines. The
problem is that when the app is closed on all machines at the same time, the
app can't compact & repair multiple times all at once, so errors occur, and
in a couple of instances, the mdb file appears to have been deleted entirely.
How can we handle this? We want to have the file compact & repair
automatically, and we don't want the end user to have to change the setting
manually on each computer.
Thanks,
Greg
Albert D.Kallal - 29 Nov 2005 02:41 GMT
You split...but likely forgot to put the front end on each pc?
Note that the compact and repair will only apply to the front end.
The back end compacting will have to be scheduled by the database team, or
the system administration personal that support and run your system.
The large corporate team of database and system admistiration personal
wearing those lab coats with pen protectors actually might be you! Either
way, someone has to be responsible for the daily backups, and system task
that need to be done.
During the development process, a developer will constantly compact during
the day. However, a production database fairs much better in this regards
Assuming a reasonably well written application, then compacting of the back
end data file can occur once a week...or even every two weeks if users are
JUST adding records to the database, and a not a lot of "churn" is occurring
(by churn, lots of record additions..and lots of record deletions). I guess
I am saying that no one rule as to how often you compact fits all
situations.
However, the rule, or suggestion that you put the front end on each computer
is a strongly recommend rule, and I explain why here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
Reading, and following the above notes on splitting will eliminate the
compacting conflict you are now experiencing.

Signature
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal