Does Compact on close wprk for the Back End?

[Frank Situmorang]

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

 

[Piet Linden]

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, doesit
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

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.

 

[DTecMeister]

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, doesit
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

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.

 

[Rick Brandt]

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.

 

[David W. Fenton]

m:

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.

This is bullshit advice, not really responsive to the question being
asked.

It's very Aaron Kempf-like advice, in fact.

 

[David W. Fenton]

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.

 

[DTecMeister]

m:







This is bullshit advice, not really responsive to the question being
asked.

It's very Aaron Kempf-like advice, in fact.

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

- Show quoted text -

David,

I wasn't being demeaning in any way. I was merely suggesting a
permanent fix for the root problem rather than furthering the band-aid
resolution trail. Using access as a front-end is great and can be
quite robust, but as a back-end, it's not what I would recommend.

Suggesting an easier and better solution does not suggest being out of
scope.
Jeff

 

[David W. Fenton]

:

David,

I wasn't being demeaning in any way.

I didn't accuse you of being demeaning. I only pointed out that your
answer was non-responsive to the original question.

I was merely suggesting a
permanent fix for the root problem

What do you define as the root problem?

rather than furthering the band-aid
resolution trail. Using access as a front-end is great and can be
quite robust, but as a back-end, it's not what I would recommend.

As a statement of principle, I think you're wrong. Jet is a great
back end for a whole host of applications.

Suggesting an easier and better solution does not suggest being
out of scope.

SQL Server is *not* an easier solution. And it's not better in 100%
of cases.

In the present instance, it's just bloody bad advice, not helpful at
all.

 

[DTecMeister]

:






I didn't accuse you of being demeaning. I only pointed out that your
answer was non-responsive to the original question.

"Aaron Kempf-like advice" = demeaning in my book.

What do you define as the root problem?

Root problem is database gets larger and larger and requires work to
get it fixed.

As a statement of principle, I think you're wrong. Jet is a great
back end for a whole host of applications.

MSAccess backend can lock up quite often and sometimes gets in a state
that even a compact and repair won't fix.

SQL Server is *not* an easier solution. And it's not better in 100%
of cases.

I woulnd't narrow the choice to SQL Server. MySQL for example is very
easy to learn, manage, and install.