|
(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.
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.
(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
|
