CompactDatabase Ain't Doing @#$%

[Guest]

I am executing DBEngine.CompactDatabase on a database in a VBA module. When
the compact finishes the compacted database is a few Mb smaller. If I go
into that database a run a compact the compacted database shrinks by more
than half.

Any guesses why CompactDatabase is failing to compact my database.

I look forward to your great help once again - BCP

 

[Guest]

Compacting via VBA can be a problem because the db has to be closed to
actually be compacted. That's why compacting from code will not fully be
compacted. When compacting manually you'll notice that access actually makes
a db1 and then renames it again to you official db name. If going through
code you should create a seperate db from which you can compact the original.

 

[Guest]

Why not just set the "compact on close" option and not worry about it?

 

[George Nicholson]

Why not just set the "compact on close" option and not worry about it?

...because in a split database situation, backends don't usually get "opened"
in such a way that "compact on close" has any effect, and frontends should
either be .mdes or contain so little data that 'compact on close' won't be
doing much of anything.

(If CompactOnClose is set in the frontend file, it will only effect the fe
file, not the backend.)

HTH,