COMPACT ON CLOSE

Good morning,

I have my database to have it compacted everytime on close. Then I realize
that from time-to-time it starts creating databases with names db1.mdb,
db2.mdb, etc. automatically, and they become permanent.

My first question is: Do you guys have your databases to compact on close?
Is it good or bad?

My second question is: Why does it create more (permanent) databases
automatically? Is it good or bad? Do I need to stop from creation and how?

Thanks.

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.

Is it good or bad?

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.

Is it good or bad?

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/ex...ributors2.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.


"AccessHelp" wrote:

Good morning,

I have my database to have it compacted everytime on close. Then I realize
that from time-to-time it starts creating databases with names db1.mdb,
db2.mdb, etc. automatically, and they become permanent.

My first question is: Do you guys have your databases to compact on close?
Is it good or bad?

My second question is: Why does it create more (permanent) databases
automatically? Is it good or bad? Do I need to stop from creation and how?

Thanks.

On Thu, 1 Dec 2005 08:01:03 -0800, "AccessHelp"
wrote:

Good morning,

I have my database to have it compacted everytime on close. Then I realize
that from time-to-time it starts creating databases with names db1.mdb,
db2.mdb, etc. automatically, and they become permanent.

Sounds like you might not have Delete privileges on the folder
containing your database. This is essential: the way Access compacts
is to copy the database to db1.mdb (or db2 if db1 exists, and so on),
delete the current database, and rename the db1. So it's not
compacting for you either I suspect!!

My first question is: Do you guys have your databases to compact on close?
Is it good or bad?

Yes. It's good or bad, depending on your situation. If you have a
split database, there's probably little need to compact the frontend
and the backend should only be compacted when nobody is using it; if
it's a single database under development, and it's WELL BACKED UP,
then regular compaction is probably a good thing.

Check your permissions. That's the most likely problem. Less likely is
some sort of corruption - check to see if there is a BackupErrors
table newly in the database. If so, ascertain why you're getting the
errors... and fix it.

John W. Vinson[MVP]

If I could can I ask a question on this. If you have a Non Networked split
database setup. When closing down the front end would the backend having
compact on close set compact the backend?

--

Joe Cilinceon


John Vinson wrote:
On Thu, 1 Dec 2005 08:01:03 -0800, "AccessHelp"
wrote:

Good morning,

I have my database to have it compacted everytime on close. Then I
realize that from time-to-time it starts creating databases with
names db1.mdb, db2.mdb, etc. automatically, and they become
permanent.

Sounds like you might not have Delete privileges on the folder
containing your database. This is essential: the way Access compacts
is to copy the database to db1.mdb (or db2 if db1 exists, and so on),
delete the current database, and rename the db1. So it's not
compacting for you either I suspect!!

My first question is: Do you guys have your databases to compact on
close? Is it good or bad?

Yes. It's good or bad, depending on your situation. If you have a
split database, there's probably little need to compact the frontend
and the backend should only be compacted when nobody is using it; if
it's a single database under development, and it's WELL BACKED UP,
then regular compaction is probably a good thing.

Check your permissions. That's the most likely problem. Less likely is
some sort of corruption - check to see if there is a BackupErrors
table newly in the database. If so, ascertain why you're getting the
errors... and fix it.

John W. Vinson[MVP]