Can I schedule a job to automatically compact and repair database?

[Shu Zong]

Hello,
Can I schedule a job to automatically compact and repair
MS Access database in Windows 2000? and how? I think I may
need some API to trigger the command in Access.

Thanks very much!

regards
Shu

 

[Douglas J. Steele]

Don't try to schedule such a job from inside of Access. Instead, schedule a
job through AT (or any other scheduler). The job you want to run is

msaccess.exe database.mdb /compact target.mdb

Include the full path to all three files. While compact will work with a
target mdb, which means that the compacted database will overwrite the old
database, I strongly recommend not doing that. If something goes wrong with
the compact, you'll lose your database. Instead, either copy the existing
database to a new file name and compact to the old file name, or else rename
the new file name after the compact.

 

[Shu Zong]

Hi Doug,
It is very nice to meet you here. You have given me much
help in the Oil company, hard to believe to get your help
again.
So it seems compacting the database could cause problem,
can you let me know why? and what is AT? Is it the Add
Schedule Task in control panel? but it can only
schedule .exe file(application), I have no place to put
the command line you gave me. Thanks a lot!

regards
Shu

 

[Tony Toews]

Can I schedule a job to automatically compact and repair
MS Access database in Windows 2000? and how? I think I may
need some API to trigger the command in Access.

As an alternative why not get the last person out or the first person
in in the morning to do a compact?

When the user exits the FE attempt to rename the backend MDB
preferably with todays date in the name in yyyymmdd format. Ensure
you close all bound forms, including hidden forms, and reports before
doing this. If you get an error message, oops, its busy so don't
bother. If it is successful then compact it back.

See my Backup, do you trust the users or sysadmins? tips page for more
info.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Douglas J. Steele]

What you get through Add Schedule Task will work. Create a BAT file that
includes the command line I showed for compacting, and, depending which way
you want to do, a COPY before or after that. Schedule that BAT file. (If
you're unable to select the BAT file to schedule, schedule anything, then go
into the Advanced options tab and change what's been scheduled).

I never said that compacting the database could cause problems: just that I
don't think you should compact into the same file. If you chose to ignore
that advice, you can simply schedule MSACCESS.EXE to run. (To add the
additional parameters, use the Advanced options tab)

As Tony said in his note, though, assuming you're using Access 2000 or
higher, you can set it to compact on close.

 

[Tony Toews]

As Tony said in his note, though, assuming you're using Access 2000 or
higher, you can set it to compact on close.

Actually no I didn't. Compact on close only works for FEs and not
BEs. So I prefer a different route.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Douglas J. Steele]

Actually no I didn't. Compact on close only works for FEs and not
BEs. So I prefer a different route.

You're right. On rereading your post, you didn't say to compact on close.
(No offense, but I stopped reading when I saw the words "last person out"
and "compact" in that first sentence. <g>)

 

[Shu]

Thanks Doug and Tony very much for your help! I got it.

regards
Shu

 

[Tony Toews]

You're right. On rereading your post, you didn't say to compact on close.
(No offense, but I stopped reading when I saw the words "last person out"
and "compact" in that first sentence. <g>)

I hear you. Umm, make that read you. I've stuck my foot umm,
keyboard in my mouth a few times in not reading postings thoroughly.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm