Decompile, Compact & Repair from VBA code.

[Jeremy Gollehon]

I wanted to Decompile, Compact & Repair from VBA code but couldn't find a
complete solution online. Here's one for you to use and for me to find with
Google Groups search in the future.

I've found that to truely compact an access mdb to it's optimal size the
following process is required. (Access 2003)

1) Compact & Repair
2) Close the file.
3) Open with a shortcut using the /Decompile switch.
4) Compact & Repair.
5) Close the file.

---------------------------------------------------------------------------
Sub TruelyCompactAndRepair()
Dim sFileName As String
Dim appAccess As New Access.Application

sFileName = "D:\Test.mdb"

'Open.
appAccess.OpenCurrentDatabase sFileName
'Compact & Repair.
appAccess.CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database...").accDoDefaultAction

'Close.
appAccess.CloseCurrentDatabase
appAccess.Quit

'Opens & Decompiles.
Shell "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE /Decompile
""" & sFileName & """"

'Close
Set appAccess = GetObject(sFileName)
appAccess.CloseCurrentDatabase
appAccess.Quit

'Opens, Compacts & Repairs, then Closes.
Shell "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE /Repair """
& sFileName & """"

End Sub
---------------------------------------------------------------------------

If you're looking at the code and wondering why it doesn't use the shell
command for the first Compact & Repair, it's because VBA doesn't wait for
the Shell command to finish when using the /Repair switch. Luckily it does
when using the /Decompile switch which allows for the use of GetObject to
close the mdb. Then a simple /Repair does steps 4 and 5 all with one simple
line of code.

Please note this code must be run from another instance of Access, or any
other VBA programming environment except the one specified in sFileName.

Hopefully someone get's some use out of this. I use it in conjuction with:
http://snipurl.com/DisallowDesignChange posted by Wayne Morgan. When
preparing to deploy a new mdb I run TruelyCompactAndRepair calling his
procedure before the first Compact & Repair.

-Jeremy