Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

General: Why CompactCurrentDatabase doesn't work from code

Author(s)
Dev Ashish & Arvin Meyer

(Q)     I'm trying to write code to compact the Current Database, same as the Compact Option in the Access 97 Menu.  But when I run the code,   Access brings up the "Enter Database to compact from/to" dialog boxes.   What am I doing wrong?

(A)    In Access 97, the menu option to Compact the current database CANNOT be duplicated from code.  Jet requires the database to be closed before any compact procedures are run.  So as long as you have any code running, the Current database is not closed, hence the compact routines cannot run. In Access 2000 and later, a new functionality was added to allow compacting the Current database in code.

        Access 2000 & Later:

                In Access 2000 and later ONLY, see this new functionality which WILL compact the current database from code.

Access 2000 & Access 97:

In Access 2000 only, if you want to automatically compact the database each time it's closed, select the Compact On Close option available under Tools | Options | General tab.

Michael Kaplan has made available a COM Addin that allows you to compact the current database and reopen the database (can be a totally different database!) in the same instance. 

   TSI SOON (Shut One, Open New) Addin

Access 97 only: Your options are to

(a)  create a small app in VB that you shell out to from your database.   This app will accept an Access mdb file, close it, run the compact routines, and re-open it in Access.  There are a few utilities available to do this.

   Compactor Addin
   mdbCompactor (VB5 utility)

(b)  create a separate "Utility" database which compacts all databases whose name/path are listed in an internal table.  For more information on how to do this,  check out

   Article 158937:
How to Compact Databases at a Scheduled Time
 

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer