How to compact your mdb file in code 2

[rewdee]

Hi All

I was wondering if I could use docmd.runcommand to compact the current mdb file. I tried the code below but I get a prompt for which database and I don't want that to appear. I just want the current one. I can't seem to pass any parameters.

DoCmd.RunCommand acCmdCompactDatabase

Does anyone know how to get this going or has a routine they could share that compacts the current mdb file.

Thanks,
Rewdee

 

[pcdaveh]

Simple answer: is that you can't
See this site:

http://home.att.net/~dashish/

Find the search box and type in "Compact" You'll find what I've pasted in below...



---Posted by Dev Ashish---

Why CompactCurrentDatabase doesn't work from code

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

The solution is to

(a) create a small app in VB that you shell out to from your database(like the one provided by Jeff Kohn). This app will accept an Access mdb file, close it, run the compact routines, and re-open it in Access;

Jeff Kohn's Home Page

mdbCompactor (VB 5 utility)

Compactor (Access 97 Addin)

or

(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 Q158937: How to Compact Databases at a Scheduled Time