General discussion

  • Creator
    Topic
  • #2074039

    Automating database compacting in Access

    Locked

    by cswearingen ·

    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?

All Comments

  • Author
    Replies
    • #3777527

      Automating database compacting in Access

      by mcorr ·

      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

    • #3777476

      Automating database compacting in Access

      by joshua miller ·

      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

    • #3783921

      Automating database compacting in Access

      by birk ·

      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.

      • #3766335

        Automating database compacting in Access

        by cswearingen ·

        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!

Viewing 2 reply threads