Compress Database

[Rob]

Using Access 2003 albeit with an Access 2000 database, which due to others
using off-site, I need to retain the 2000 format.

I regularly clear data from a table and populate with new data and as such,
the size of the file grows and grows. I can manually compress but would
like to compress on either a click of a button, or when the file opens or
closes.

If anyone has some sample code, could it be posted please. I'd like the
code to refer to the current database regardless of what drive or directory
it is in, this way others can use even if they have renamed the file.

Regards, Rob

 

[Tom Wickerath]

Hi Rob,

I regularly clear data from a table and populate with new data and as such,
the size of the file grows and grows.

You might want to try out the idea of using a temporary work database, which
is linked on-the-fly to your application. You can set this up so that the
temporary work DB is created when you open a specific form, and deleted when
you close the form, or make it so that it is created when you open your
application, and is deleted when you close your application. Here is a sample
download that I have made available, which demonstrates the first method
(when form is opened/closed):

http://www.accessmvp.com/TWickerath/downloads/tmpwrkdb.zip

I can manually compress but would like to compress on either a click of a
button, or when the file opens or closes.

It's not possible to have code to compact the database that is open.
However, you can implement the idea of a "helper" application, a separate
application used to start your application. Former Access MVP Jeff Conrad has
an example available that shows how to do this:

Creating A Customized Login Screen For A Secured Database
http://www.access.qbuilt.com/html/custom_login.html


It is possible to set the Compact on Close option, found under Tools >
Options, but I don't recommend doing so. For one thing, it compacts every
time you close, whether you need to do so or not. Also, you aren't given the
chance to create a back-up first, before compacting. Creating a back-up is
always a good thing to do before compacting.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Rob]

Thanks Tom and Chris, some good ideas that I'll try out.

Regards, Rob

 

[Tom Wickerath]

Hi Rob,

You're welcome. I probably should have mentioned that the information in the
article I referenced that Jeff Conrad wrote can be found by scrolling down
about 3/4 of the way. You should see a heading that reads "A Custom Login
Form Can Provide Multiple Solutions". That may not have been evident, if you
opened the link and noticed that it dealt with a customized login form.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Lars Brownie]

Chris,

Since I don't backup before 'compact and repair', I'm kinda worried.
When compacting doesn't succeed, will Access tell you? Or is there a way to
verify it?
Because if you find out a month later, it will be very hard to restore the
old version and add last month's data as well.

Thanks, Lars

 

[Tom Wickerath]

Hi Lars,

When compacting does not succeed, the result may simply be a database that
is not compacted, often times with extra copies of the database in the same
folder, ie. DatabaseName_1, DatabaseName_2, DatabaseName_3, etc. You may or
may not get an error message. Here is an example of an error message you
*may* [or may not] get:

http://support.microsoft.com/?id=818099

At other times, when a compact fails, you may be the unlucky one whose
database has been turned into cottage cheese. In fact, there have been
several reports of the total loss of the database when compacting with Access
2007. Don't have a recent back-up available when that happens? In that case,
sing this little song to yourself:

Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.

Suddenly,
There's not half the files there used to be,
And there's a panic
coming over me
The system crashed so suddenly.

I pushed something wrong
What it was I could not say.

Now all my data's gone
and I long for yesterday-ay-ay-ay

Yesterday,
The need for back-ups seemed so far away.
I knew my data was all here to stay,
Now I believe in yesterday.


Author Unknown



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Lars Brownie]

Thanks for the info. At least there is an indication when something goes
wrong.

Lars

Hi Lars,

When compacting does not succeed, the result may simply be a database that
is not compacted, often times with extra copies of the database in the
same
folder, ie. DatabaseName_1, DatabaseName_2, DatabaseName_3, etc. You may
or
may not get an error message. Here is an example of an error message you
*may* [or may not] get:

http://support.microsoft.com/?id=818099

At other times, when a compact fails, you may be the unlucky one whose
database has been turned into cottage cheese. In fact, there have been
several reports of the total loss of the database when compacting with
Access
2007. Don't have a recent back-up available when that happens? In that
case,
sing this little song to yourself:

Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.

Suddenly,
There's not half the files there used to be,
And there's a panic
coming over me
The system crashed so suddenly.

I pushed something wrong
What it was I could not say.

Now all my data's gone
and I long for yesterday-ay-ay-ay

Yesterday,
The need for back-ups seemed so far away.
I knew my data was all here to stay,
Now I believe in yesterday.


Author Unknown



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Chris,

Since I don't backup before 'compact and repair', I'm kinda worried.
When compacting doesn't succeed, will Access tell you? Or is there a way
to
verify it?
Because if you find out a month later, it will be very hard to restore
the
old version and add last month's data as well.

Thanks, Lars