NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB

[mdlueck]

I have developed a helpful Windows NT Command Script to drive the Decompile / Compact / Compile process. It now detects the file size of the database before/after running Access, and if (still) different loops and runs the Access decompile process again. At times I have needed to preform the following steps 10 times in order to have Access finally get done with its work completely.

These steps are optimized for an Access 2007 environment.

Suggested Decompile Steps:

1) The database should be in the same directory on the C: drive as the decompile.cmd script attached. Update the script as necessary to make correct for your working environment.
2) Run the decompile.cmd script which will start Access, the database, and Access will decompile it.
3) Next close the database – not all of Access.
4) Then reopen the database. (Remember the shift key if you have an autoexec macro!)
5) Compact the database at this point (MS Office icon \ Manage \ Compact and Repair) (Remember the shift key if you have an autoexec macro!)
6) Press Ctrl+G to open the VBA window
7) Click the Debug menu \ Clear All Breakpoints
8) Click the Debug menu \ Compile - ONLY do this step the FIRST time!
9) Then Compact again as in Step 6 (Remember the shift key if you have an autoexec macro!)
10) Completely exit Access (Remember the shift key if you have an autoexec macro!)

When the before / after file size are finally the same, the decompile.cmd script will end.

Note: I found that only doing step 8) the first time through results in a completely decompiled database. This is much smaller (in my case an FE DB compiled of 18MB reduces to 13MB completely decompiled) and also completely avoids the nasty "big bang upgrade" encountered when crossing the Windows 7 SP0 to Windows 7 SP 1 / Office 2010 SP0 to Office 2010 SP1 divide talked about on this thread:

Debugging Error
http://www.access-programmers.co.uk/forums/showthread.php?t=229877

I have successfully deployed my biggest / most complicated FE DB which makes extensive use of ADO objects, and the TreeView OCX control (v6), and and and... prepared via these documented steps on Windows XP / Office 2007 directly to a Windows 7 SP1 x64 / Office 2010 SP1 x86 machine and it launched BEAUTIFULLY without any tinkering necessary!

Source code for decompile.cmd as follows:

@ECHO OFF
REM /************************************************************************************/
REM /* FILENAME       :  DECOMPILE.CMD                                                  */
REM /* TYPE           :  Windows NT Command Script                                      */
REM /* DESCRIPTION    :  This module drives the Access DB Decompile process             */
REM /*                                                                                  */
REM /* AUTHOR         :  Michael D Lueck                                                */
REM /*                   mlueck@lueckdatasystems.com                                    */
REM /*                                                                                  */
REM /* NEEDS          :                                                                 */
REM /*                                                                                  */
REM /* USAGE          :                                                                 */
REM /*                                                                                  */
REM /* REVISION HISTORY                                                                 */
REM /*                                                                                  */
REM /* DATE       REVISED BY DESCRIPTION OF CHANGE                                      */
REM /* ---------- ---------- -------------------------------------------------------    */
REM /* 10/28/2011 MDL        Initial Creation                                           */
REM /* 11/02/2011 MDL        Updated to parameterize and also display the pre/post size */
REM /* 12/27/2011 MDL        Updated to parse out the filesize and do the compare       */
REM /* 07/03/2012 MDL        Update to make UserID independent                          */
REM /* 01/21/2013 MDL        Pre request of April15Hater, updated to make safe for DB   */
REM /*                       filenames containing space characters                      */
REM /************************************************************************************/

REM Support for multiple database files within the one directory
REM Simply unREM the correct LOC to decompile that database file
SET DBfile=Fandango_FE_2007.accdb
REM SET DBfile=JDEFandangoReplicate.accdb
REM SET DBfile=SchemaIdeas.accdb
SET DBfile=Schema Ideas.accdb

ECHO.
ECHO This script will Decompile the %DBfile% database.
ECHO.
ECHO Do you want to do that?
ECHO.
ECHO If NO, then Ctrl-Break NOW!
ECHO.
ECHO Please remember to hold down the shift key to prevent Access from running
ECHO the autoexec macro if there is one in the database being decompiled.
ECHO.
PAUSE

:RunDecompile
FOR /F "delims=" %%A IN (' dir  /a-d/b "%DBfile%" ') DO (
  SET DBfilesizepre=%%~zA
  SET DBfilefullyqualified=%%~fA
)
ECHO File Size pre-decompile:  %DBfilesizepre%

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile "%DBfilefullyqualified%"

FOR /F "delims=" %%A IN (' dir  /a-d/b "%DBfile%" ') DO (
  SET DBfilesizepost=%%~zA
)
ECHO File Size post-decompile: %DBfilesizepost%

IF %DBfilesizepre% == %DBfilesizepost% GOTO End

ECHO File size is different, so running decompile again...
ECHO.
GOTO RunDecompile

:End
ECHO Decompile Completed!