Compacting Question

[FLabrecque]

I want to compact the database currently opened with VBA code. I do a lot if import, update and delete in a very amount of time. By comptacting, I hope to reduce the filesize of my DB.

In the last line of my VBA function, I added:

DBEngine.CompactDatabase "M:\ACCESS\REPORTER.MDB", "M:\ACCESS\REPORTER.NEW"

This doesn't work, since my database is already opened. Am I using my CompactDatabase method correctly? My helpfile is currently corrupted and need to be re-installed (you-hou). If that doesn't work, is there something else that will?

 

[ghudson]

What version of Access are you using?

 

[FLabrecque]

MS-Access 2000.

 

[ghudson]

Then why not turn on the Compact On Close option to always compact the when the last person closes it?

From the menu bar in Access you need to click the following options:
Tools \ Options \ General \ [select] Compact On Close

 

[FLabrecque]

Because I open it and close it around 20-30 times per day. I don't have the patience to wait 30-40 seconds each time.

 

[ghudson]

That is understandable.

I use a custom shortcut to compact my db's on demand. This is the target data of the shortcut:

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "X:\MyDatabase.mdb" /repair /compact /excl

Note: The repair switch is not valid for Access 2003 [not sure about 2000]