Does Compact on close wprk for the Back End?

Hello,

I see that the BE database keep bigger in size. My question is if we setup
the option to be " Compact on Close" on each Front End of the users, does it
work to compact the backend?. How can it be while the other users is closing,
while the others are still using, how will it disturb the current BE when
compacting??.

Appreciate any idea provided
--
H. Frank Situmorang

On Sep 24, 10:57*pm, Frank Situmorang wrote:
Hello,

I see that the BE database keep bigger in size. My question is if we setup
the option to be " Compact on Close" on each Front End of the users, does it
work to compact the backend?. How can it be while the other users is closing,
while the others are still using, how will it disturb the current BE when
compacting??.

Appreciate any idea provided
--
H. Frank Situmorang

You have to set the backend to compact on close and then force it to
close when nobody's in it. You could use a form in the backend to run
code to poll for activity and then run a close/compact/reopen.

On Sep 24, 11:57*pm, Frank Situmorang wrote:
Hello,

I see that the BE database keep bigger in size. My question is if we setup
the option to be " Compact on Close" on each Front End of the users, does it
work to compact the backend?. How can it be while the other users is closing,
while the others are still using, how will it disturb the current BE when
compacting??.

Appreciate any idea provided
--
H. Frank Situmorang

Of course your best bet is to move the data to a different ODBC
compatible database and link the tables so MSAccess is only your front-
end to a much more robust back-end database.

Piet Linden wrote:
On Sep 24, 10:57 pm, Frank Situmorang wrote:
Hello,

I see that the BE database keep bigger in size. My question is if we
setup the option to be " Compact on Close" on each Front End of the
users, does it work to compact the backend?. How can it be while the
other users is closing, while the others are still using, how will
it disturb the current BE when compacting??.

Appreciate any idea provided
--
H. Frank Situmorang

You have to set the backend to compact on close and then force it to
close when nobody's in it. You could use a form in the backend to run
code to poll for activity and then run a close/compact/reopen.

The idea of forcing a file to close when nobody is in it is silly because if
nobody is in it then it is not open. You also cannot make a file close and
then re-open itself because once the file is closed there is no more code
running to re-open it. You also cannot make it compact itself via code.

You can compact the back end using code in the front end providing the back
end is not being used. You would check for that by testing for the presence
of the LDB file in the same folder.

As to the original question, no, compact on close does not compact the back
end (the file that might actually need it) which is (one reason) why compact
on close is not a good idea.

Compacting is an occassionally (though regularly) needed maintenance
activity that should be performed manually as appropriate. It is not a
set-it-and-forget-it process.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

=?Utf-8?B?RnJhbmsgU2l0dW1vcmFuZw==?= wrote
in :

I see that the BE database keep bigger in size. My question is if
we setup the option to be " Compact on Close" on each Front End of
the users, does it work to compact the backend?. How can it be
while the other users is closing, while the others are still
using, how will it disturb the current BE when compacting??.

Compact on close is both useless and dangerous:

1. if your app is split (as it should be), COMPACT ON CLOSE will
compact only the front end, and front ends just don't need to be
compacted, since no data is stored in them and they don't grow
beyond a certain point after they've been in use.

2. some databases that can be opened but are in a suspect state
(i.e., there is corruption present but it's not preventing the
contents of the database from being used) when compacted will lose
data that was previously accessible. In other words, a compact that
you can't cancel (as with COMPACT ON CLOSE) could cause you to lose
data that would otherwise be recoverable absent a compact.

There is no circumstance under which COMPACT ON CLOSE should be
turned on. Indeed, Microsoft should completely remove it from Access
because when it is not a waste of time, it is downright dangerous to
your data.

On the issue of compacting your back end, you need to do it via some
other method. Possibilities include:

1. have a command button somewhere in your front end that will
compact the back end for you. This requires that no other users be
connected to the database at the time of the compact, and that you
have closed all data-bound forms in your front end.

2. have a process that runs on a schedule on your server that
compacts the back end on a regular basis. This is easily enough
programmed with VBScript, but I'd recommend also adding in some
backup, so that if your compact causes problems, you can restore the
previous version.

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