Compact DB on close

[erick-flores]

Hello

In an Access 2003 database I have the Tools > Options > General:
Compact Database (checked) enable.
When I use the database in my local compauter it compacts when it
closes just fine. BUT when I move the files to the network, and run
the database from a network location, when it closes it compacts
creating a new .mdb (db1.mdb) file with the compacted database,
leaving the old .mdb file w/o compacting. If I open the database again
(from the network location) it will create another and another and
another .mdb file (db2.mdb, db3.mdb, db4.mdb, etc.) always leaving the
old .mdb growing and growing in size.

Can somebody please tell me whats happening here?

Thanks in advance

 

[Douglas J. Steele]

Compact On Close is generally considered to be a bad idea. Compacting that
frequently is not necessary, and can actually even lead to database
corruption.

Applications should always be split into a front-end (containing the
queries, forms, reports, macros and modules), linked to a back-end
(containing the tables and relations). Only the back-end should be on the
server: each user should have his/her own copy of the front-end, ideally on
his/her hard drive. Since the bulk of the changes happen in the back-end,
it's the one that needs to be compacted on a regular basis, and having
Compact On Close set on the front-end won't accomplish that.

 

[Tom Wickerath]

Hi Erick,

There is a compact error in the process. When you start the compacting
process, a new database (db1.mdb) is created. If there is already a file in
the same folder, with the same name, then a higher number is used (ie.
db2.mdb, or, if this is already present, db3.mdb, etc.). Only when the new
file is successfully created is the original file suppose to be deleted, and
the dbX.mdb file renamed to the original filename. But something is hanging
up in this process, preventing it from completing properly.

In general, it is not a good idea to use the Compact on Close option. For
one thing, per Microsoft's own advice elsewhere, one should always make a
backup copy before compacting. This really isn't possible if you have this
option enabled.

BUT when I move the files to the network, ....

You should consider splitting the file into two files: a front-end (FE)
application file and a back-end (BE) data file. Only the BE file is generally
moved to the network. More information about this is available in the second
paragraph of my Multiuser Applications paper, available here:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________