Compacting and Repairing an Access File

[Jose Espejo]

During the weekend my co-workers were inputing information
into the Access database and deleting old records that
were erronious or duplicates. They ran the Compacting and
Repairing an Access File from the tools menu and the DB
changed from 20.1MB to one DB of 14.6MB and another of
14.6MB. I checked both DBs and found that both were
complete databases. Does Compacting and Repairing an
Access File chrink the file this much? and does it create
a backup copy of itself?

Your answer will be most appreciated.
Jose

 

[Wayne Morgan]

The compact and repair should be routine maintenance and yes, it can cause
the file to reduce in size that much, especially if they had been deleting
records.

No, it doesn't create a backup. It is always a good idea to keep a backup,
but you'll need to set up a routine to do that yourself. You can use a batch
file to copy the file to a backup folder, a tape backup, or just manually
copying the file. The more automated the approach the better so that it
doesn't get forgotten. Just remember, most programs can't access the file if
it is open, so if someone is using it, you may not be able to get a good
backup copy.

 

[Paul Sutton]

What you might have been seeing is this...
When access compacts, it creates a new database it calls
db1.mdb, moves everything to it, deletes the file you were
compacting and renames itself to the original filename -
so for a time, there are two files.

 

[Tom Wickerath]

To add to Wayne's answer, I was once asked to help with a query issue on a shared Access
database. The back-end DB, on the file server, was over 67 MB in size. I did a compact &
repair operation and it shrunk down to about 2.5 MB. I don't think anyone had ever
bothered to compact this database!

Compacting the database has several benefits. One of them is that it will decompile all
queries, and force them to be re-optimized the next time they are run. This allows Jet to
determine the most efficient plan for running each query.

Tom
_________________________________________


The compact and repair should be routine maintenance and yes, it can cause
the file to reduce in size that much, especially if they had been deleting
records.

No, it doesn't create a backup. It is always a good idea to keep a backup,
but you'll need to set up a routine to do that yourself. You can use a batch
file to copy the file to a backup folder, a tape backup, or just manually
copying the file. The more automated the approach the better so that it
doesn't get forgotten. Just remember, most programs can't access the file if
it is open, so if someone is using it, you may not be able to get a good
backup copy.

--
Wayne Morgan
Microsoft Access MVP


_________________________________________

 

[Cascoschi.rosario]

tank you

file