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.

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"dch3" <(E-Mail Removed)> wrote in message
news:CC37AE4C-7584-41A8-BCC3-(E-Mail Removed)...
> 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.

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.

"Douglas J. Steele" wrote:

> 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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "dch3" <(E-Mail Removed)> wrote in message
> news:CC37AE4C-7584-41A8-BCC3-(E-Mail Removed)...
> > 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.
>
>
>

=?Utf-8?B?ZGNoMw==?= <(E-Mail Removed)> wrote in
news:3E52F28E-A8B2-4A29-A29D-(E-Mail Removed):

> 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).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

....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.

"David W. Fenton" wrote:

> =?Utf-8?B?ZGNoMw==?= <(E-Mail Removed)> wrote in
> news:3E52F28E-A8B2-4A29-A29D-(E-Mail Removed):
>
> > 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).
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/
>

=?Utf-8?B?ZGNoMw==?= <(E-Mail Removed)> wrote in
news:543D8C69-33B8-4662-8A70-(E-Mail Removed):

> ...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).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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" wrote:

> =?Utf-8?B?ZGNoMw==?= <(E-Mail Removed)> wrote in
> news:543D8C69-33B8-4662-8A70-(E-Mail Removed):
>
> > ...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).
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/
>

"Linq Adams via AccessMonster.com" <u28780@uwe> wrote in
news:8870eb434e626@uwe:

> 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/