Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    2

    Unanswered: Sub that will automatically compact a database when a user exits?

    I am fairly new to Access, so this may be a question worthy of a llama, but does anyone know of a way to compact a database automatically whenever it is shutdown? I have users that are creating 1.0GB databases... and showing them how to do this manually isn't working.

    Thanks!

  2. #2
    Join Date
    Jul 2002
    Posts
    67
    I don't know of any routine that would compact a database automatically upon exit of the database.

    However...I believe you can't compact a database when other users are still using it. So if you were to find a routine that would auto compile for you, you would have to make sure that no one else had an open connection to the database.
    Don't do today, what can be done tomorrow.

  3. #3
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    If you are using Access 2000 you can use feature "Compact on close":

    1. Open your MDB file.
    2. Go Tools | Options on menu bar.
    3. Click General Tab.
    4. Tick Compact on Close checkbox.

    That's it.

    Igor

  4. #4
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    You could use DBEngine.CompactDatabase, but the problem is that you cannot use it in the Compacted Database, you can compact another Database (is noone is using it), but not the current Database. However, you can compact a Linked Database, since your users increase the databases size by adding datas, then just use Two Separate mdb, one for the datas, and another for the Application (Form, query, etc.), then you'll be able to compact the Databases containing the tables (But you should make sure the database is not compacted all at the same time at the end of the day, one time by day is enough..., you can do that by adding a parameters table with a field LastCompacted)

    JefB - hope it helps

  5. #5
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    2

    Thumbs up

    Thanks All,

    I am using Access 2000 so Igor's suggestion will work for me. I would like to point out that the simplicity of this solution confirms that I am truly, a llama. However, I am a very appreciative llama.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •