How do I automate compacting my database?

  • Thread starter Thread starter Kelvin Beaton
  • Start date Start date
K

Kelvin Beaton

Is this the proper use of the Compact switch?

"C:\Program Files\Microsoft Office\Office\msaccess.exe" "C:\Documents and
Settings\user\Desktop\TCTDatafiles.mdb" /compact

How do I compact and repair my database as a scheduled task? I know how to
create the task, but it seems line when I run this command it makes a copy
of the database and leaves the old on in place. Is this the correct approach
or should I be approaching this from a different angle?

I'd like to have a task run automatically on a regular basis, what is the
best way to approach this?

Thanks

Kelvin
 
Kelvin said:
Is this the proper use of the Compact switch?

"C:\Program Files\Microsoft Office\Office\msaccess.exe" "C:\Documents and
Settings\user\Desktop\TCTDatafiles.mdb" /compact

How do I compact and repair my database as a scheduled task? I know how to
create the task, but it seems line when I run this command it makes a copy
of the database and leaves the old on in place. Is this the correct approach
or should I be approaching this from a different angle?

I'd like to have a task run automatically on a regular basis, what is the
best way to approach this?

Thanks

Kelvin
Click to expand...
Hi Kelvin,

You don't say which version you are running. But from A2K on up there is
a "Compact on Close" option available:

Tools > Options
Select the "General" tab
Check Compact on Close
Click OK

This will automatically run a Compact/Repair whenever you close the
database.

hth,

LeAnne
 
Thanks for the reminder, but since it is a database that multiple people
use, and not any one person would be the last person closing it, I was
hoping to schedual a task to run after everyone has gone home.

So I guess I'm still use a command line option in a task.

Thanks

Kelvin
 
If your database is setup with a Front End on each users computer and a
backend DB on a network server, this should not be a huge problem. What tends
to bloat DBs worse than anything else is the use of temp tables. Access does
a pore job managing file size. When disk space is allocated for temporary
data that is later deleted or replaced, the disk space is not released. New
data just causes access to grab more disk space. Put the temp tables in the
Front End. Backend DB is only for data that you want stored. Obviously if you
delete a stored record, that disk space will not be released automatically,
but if you restrict as much as possible the deleting of records, this can be
managed.

I have developed a DB where I have a table that shows who is currently
logged on. Each user logs on with user name and password. When they do this
there username and the current date are entered into a table. They do what
they need to do, then log out. When they log out, the record with their
username and password are removed from the table.

You will probably still need to compact the backend db from time to time,
but if you have the Frontend Backend design architecture then you should be
able to minimize the compacts needed for the backend. If you dont have this
architecture and don't want to change it, try requireing everyone to login
and record their username and password as I described. Then have another
access app that runs say on your machine. Start it up when you leave for the
day. It does not need to be complicated. Have one form with an ontimer event.
that runs a VB module. In the module run a record set query to check if
anyone is still logged in. If they are it does nothing. If there is nobody
currently logged in, it compacts the db and then quits. If you don't quit the
compact utility, then it will continue to compact and will tie up the db.

Hope this helps!

Kevin
 
Back
Top