any way to schedule a compress/repaor task?

[Coolpapabell]

If I leave my access application open overnight is there a way to schedule a database compress/repair?
If not within access, has anyone used a old fashioned script writer to open the database and compress it?
Maybe even have the database open and close itself doing the compress on shutdown?

Back end database is just getting too big without regular compression and its slowing up everything.

[Zigzag]

Use windows scheduler to open the BE at a set time ie 01:00 AM
Set the BE start options to open a form. Set the form open or load events to copy DB and then quit the DB with options to compact and close.
Ive done this with 2003 but not with 2007.
If you need VBA for this let me know.

[Coolpapabell]

Thank you zigzag!
1 question before I start testing.
The database in question is a back end which is opened by other front end application databases that link to its tables.

When a display form is selected for a database, does the form only open when a user opens the database or would the form also open when another database opens it to use its linked tables?

Thanks again,
Ari

[Zigzag]

Front Ends only link into the Backend tables, they don't open it.
If you had a form in the backend it would only display/open when the DB was opened.
It's not considered best practice to have a Backend with forms in but I don't consider this to be a problem.

[Zigzag]

Also remember that the backend will not compact and repair if a front end is still open and linked into it.
All frontends must be closed before a compact and repair will work.

[martyn]

You can use the options and repair and compact everytime you exit access. Located in the tools.

[Coolpapabell]

You can use the options and repair and compact everytime you exit access. Located in the tools.

Is it safe to do this without backing up first? The files are backed up everyday but its a transaction heavy system and doing a recovery is never fun.

It's not considered best practice to have a Backend with forms in but I don't consider this to be a problem.

Is there a way to have 1 database compact another?

Thanks again, Ari

[Zigzag]

I create my DBs to Backup every day, each backup is seperate for the day of the week, ie Monday is MYDB(Backup2).mbd, Tuesday is MYDB(Backup3).mbd etc etc.
I prefer to backup before the compact and repair.

Is there a way to have 1 database compact another?

I think there is but I have never used that aproach because you cant campact and repair a DB that you are linked to and if you are not linked to it you might as well get windows scheduler to do it for you.

These are just my opinions