My first question is: Do you guys have your databases to compact on close?
No. In our environment, this is unnecessary. We compact "as needed" (such
as after intensive updates and deletes) and periodically, like once a week.
It depends upon your environment. If everyone is sharing a database file on
the network, then compacting on close is a bad idea. (The database should be
split so that users aren't sharing the front end. "Compacting on close" for
a single user database affects no other users when the file is being
compacted.) It's a good idea to have the single user database compact
automatically if people are forgetful and don't do it themselves.
However, it's the back end that usually needs to be compacted, and just
connecting to it via linked tables isn't going to compact the back end when
the user exits the front end. Opening the database via the Access user
interface is required before the "compact on close" option is exercized.
Why does it create more (permanent) databases
automatically?
The compaction failed, more than once. Usually this is due to a multiuser
database being shared and the "compact on close" option is set. When one
user exits, the database attempts to compact so creates a new db1.mdb file to
place the compacted version of the current file into, but since the user
doesn't have exclusive access to the file while others are still connected,
the compaction isn't completed, so the redundant (and incomplete) db1.mdb
file remains in the same directory. The next time this happens, db1.mdb
already exists, so db2.mdb is created, and so on.
If the situation is as I've described for a shared multiuser database, then
it's bad. (Bad because of the configuration, not the "compact on close.")
The Microsoft Access team has determined that the number one cause of
corrupted Jet database files is multiple users sharing a single Jet database
file across the network. If the compaction is failing for another reason,
then you need to determine why. (Out of disk space? Exceeding disk quota?
et cetera)
For more information on splitting and compacting the database, please see
the following Web page:
http://www.Access.QBuilt.com/html/gem_tips.html
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
