DBEngine.CompactDatabase vs Compact on Close

[dch3]

If I implement DBEngine.CompactDatabase when my database closes (as opposed
to the Compact on Close option), are there any caveates that I should be
aware of?

I want to give my users a message that the database is shutting down as
opposed to the Access Application Window just sitting there until the DB is
compacted.

I'm also fully aware that I'll have to build the code into an Exit routine
and then force the user's to Exit via the routine - I'm in the process of
implementing custom Menu Bars.

 

[Douglas J. Steele]

I'd question doing either.

Your application should be split into a front-end (containing the queries,
forms, reports, macros and modules), linked to a back-end (containing the
tables and relationships). It's the back-end that needs compacting, and
Compact On Close only works on the "active" database (the front-end). If the
front-end becomes bloated, simply replace it with a new copy.

As well, I personally don't think it's a good idea to compact that
regularly. The decision to compact should be based on how much the database
has grown, or how much it's been used.

 

[dch3]

The database is split. However, there are several temp tables located in the
front end that are regularly appened to and then deleted. The nature of the
tables is such that they can't reside in the backend as they support a
user-based wizard. As such, a copy of the front end will be located on 30 or
so PC'S.

 

[David W. Fenton]

The database is split. However, there are several temp tables
located in the front end that are regularly appened to and then
deleted. The nature of the tables is such that they can't reside
in the backend as they support a user-based wizard.

Put the temp tables in another MDB, linked in your front end. Then
you can replace the temp MDB with a pristine copy with a file copy
operation, say, every time you close the app. Or, you can just leave
it alone (I started out replacing it on close, but now don't bother
at all).

 

[dch3]

....and to think I've already been working on a VBScript file to automatically
check the front end on the server to see if its a newer version that the one

 

[David W. Fenton]

...and to think I've already been working on a VBScript file to
automatically check the front end on the server to see if its a
newer version that the one on the local machine.

The only complication with this is distributing to a new location,
where you need to relink. If you place the tmp.mdb in the same
folder as the front end, it's easy. If you put it somewhere else, it
can be complicated. Because of that, I created my own reconnect
utility that manages reconnecting to multiple back ends easily:

http://dfenton.com/DFA/download/Access/Reconnect.html

All of my apps use this (because all my apps have temp tables).

 

[dch3]

I was thinking about having the temp *.mdb in the same folder as the front
end. Somewhere, I've got code that extracts the full path to a file and was
going to use that to obtain the path to the front end folder (via the DB
property whose name I can't remember at the moment) and temp .mdb when I
reestablish the links if need be.

 

[David W. Fenton]

Also be aware that many developers believe that, paradoxically,
Compact & Repair is actually a source of repeated corruption! As
such, C&R should only be done after making a backup copy of your
database.

I don't know of anyone that believes a compact could actually
corrupt a database that is not already corrupt. It is possible for a
compact to make a silently corrupted database inaccessible, or it
could cause data loss in the compacted result. But that's not the
same thing at all as suggesting that the compact *caused* the
corruption. It simply flushed it out, or revealed what was
previously hidden.

Either way, it's essential that you have a backup before any compact
operation, regardless of what you think about the "dangers" of
compact.