Compact Database on Close - Good Idea or Not?

[Susan Bricker]

Greetings. I just discovered the 'Compact on Close' option in
Access-Tools-Options-General Tab. It certainly sounds like a smart
idea to me. But before I turn that option on I thought I'd check with
the experts to see if there are any reasons why I would not want to
'Compact on Close'. Can you think of any?

Thanks.

SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Steve Jorgensen]

On Tue, 19 Apr 2005 15:17:02 GMT, Susan Bricker <sl*****@verizon.net> wrote:

Greetings. I just discovered the 'Compact on Close' option in
Access-Tools-Options-General Tab. It certainly sounds like a smart
idea to me. But before I turn that option on I thought I'd check with
the experts to see if there are any reasons why I would not want to
'Compact on Close'. Can you think of any?

Thanks.

SueB

The option can be useful for a stand-alone database, but it's not so useful
for a split front-end/back-end app (all of mine are). The reason is that
compact on close only closes the front-end database, and it's usually the
back-end that really needs the compacting.

[Susan Bricker]

Steve,

Thank you for the explanation. Is there a way (programatically) to
compact a back-end app? I do have an application (here at work) that is
split (back-end and front-end). Luckily, I have the ability and
authority to get to both and can manually compact them but it would be
great to have a command button or some action kick-off the database
compact routine. Do you have any ideas or suggestions?

SueB
*** Sent via Developersdex http://www.developersdex.com ***

[Di]

Hi Susan,
Most of my applications are separated front and backend due to
multiple users. To compact back end only, I use 2 methods:
1) Assign a time for compact and the first person who opens the
front end after that time takes the hit (ie the wait for backup and
repair).
2) Assign a volunteer :o) to do this via button.

To code using vba:

If you have multiple users, you must check if the database lock file
exists, if this exists the database will not compact as it is in use.

It would be a good idea to have a menu page in the UI/FE which is not
attached to any tables which users can back out to when they are not
entering data as most people do not like to close and reopen especially
if you have security, this will leave the backend free for compact.

If (Not (FileExists("filename.ldb"))) Then
DBEngine.CompactDatabase "Filename.mdb", Temp
FileCopy Temp, "Filename.mdb"
End If

This creates a compacted temp database and overwrites your existing BE
database with it. Remember to use :
If (FileExists(Temp)) Then
Kill Temp
End If
first to remove old copies of temp.

This works for me.
You can find good explanations, other methods and helpful code on
"http://www.mvps.org/access/"

Good Luck,
D...

[Susan Bricker]

Di,

Thanks much for the info. It looks like it will work for me, also.

SueB
*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On Tue, 19 Apr 2005 15:31:04 GMT, Susan Bricker <sl*****@verizon.net>
wrote:

An alternative to Di's method works if your server machine is on at
all times, whereas the client machines are only on during the day.
This involves scheduling a Windows Task to run automatically at night,
using a command line like this:
<path to>msaccess.exe <path to>my.mdb /compact

-Tom.

Steve,

Thank you for the explanation. Is there a way (programatically) to
compact a back-end app? I do have an application (here at work) that is
split (back-end and front-end). Luckily, I have the ability and
authority to get to both and can manually compact them but it would be
great to have a command button or some action kick-off the database
compact routine. Do you have any ideas or suggestions?

SueB
*** Sent via Developersdex http://www.developersdex.com ***