Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Automating database compacting in Access

Locked

I know that I have seen a tip somewhere that tells how to compact an Access97 database automatically. I just can’t remember where I saw it or how to do it. Any help?

Topic

Automating database compacting in Access

In reply to Automating database compacting in Access

You can use any scheduler to call a command line like “c:\…\msaccess.exe dbname.mdb /compact”. I use Norton Antivirus’ scheduler to compact several databases on a scedule.

Do you mean automatically compact when you open? This would be a problem is a multi-user database. You might be able to use the compactDatabase method, but it only works on closed databases.

Chris

Automating database compacting in Access

In reply to Automating database compacting in Access

Automating database compacting in Access

In reply to Automating database compacting in Access

Try going into your database and Choose Tools > Options > General Tab > Compact on Close Checkbox. This works well.

Joshua L. Miller
Summit Computer Systems

Automating database compacting in Access

In reply to Automating database compacting in Access

Automating database compacting in Access

In reply to Automating database compacting in Access

Here is Access code to compact a database that you can put in the application Close event (or any other place you want to use it):

current$ = “c:\enrollmenttempdump.mdb”
temp$ = “c:\\enrollmenttdsmall.mdb”
DBEngine.CompactDatabase current$, temp$ ‘ _ compact tempdump to temp$
Kill current$
Name temp$ As current$

current$ references the MDB file you want to compact; temp$ is a temporary file it gets compacted to. If the MDB file to be compacted is big or highly fragmented you might want to change the cursor to an hourglass during the compacting process. Also, the MDB file has to be closed because this code deletes it.

Automating database compacting in Access

In reply to Automating database compacting in Access

I wanted to keep the user from having to do the compacting. This solution is the closest to what I want to do. I did find a utility on Dev Ashish’s site to do this same thing. I can even call it as a function with arguements so that it is easier (in some respects). Thanks for the help!

[Author]

Replies