Compact repair data.vbs

This is VBS code to compact and repair a .mdb:

   ' *****************  BEGIN CODE HERE  ' *****************
   '
   Dim objScript
   Dim objAccess
   Dim strPathToMDB
   Dim strMsg
   '
   ' ///////////// NOTE:  User must edit variables in this section /////
   '
   '  The following line of code is the only variable that need be edited
   '  You must provide a path to the Access MDB which will be compacted
   '
           strPathToMDB = "C:\ccmdb_data\ccmdb_data.mdb"
   '
   ' ////////////////////////////////////////////////////////////////
   '
   ' Set a name and path for a temporary mdb file
    strTempDB = "C:\ccmdb_data\tmp.mdb"
   '
   ' For Access 2003, use Application.11
   Set objAccess = CreateObject("Access.Application.11")
   '
   ' Perform the DB Compact into the temp mdb file
   ' (If there is a problem, then the original mdb is  preserved)
   objAccess.DbEngine.CompactDatabase strPathToMDB,strTempDB,,,";pwd=4Cookies"
   '
   If Err.Number > 0 Then
       ' There was an error.  Inform the user and halt execution
       strMsg = "The following error was encountered while compacting database:"
       strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
   Else
       ' Create File System Object to handle file manipulations
       Set objScript= CreateObject("Scripting.FileSystemObject")
   
       ' Back up the original file as Filename.mdbz.  In case of undetermined
       ' error, it can be recovered by simply removing the terminating "z".
       objScript.CopyFile strPathToMDB , strPathToMDB & "z", True
       '
       ' Copy the compacted mdb by into the original file name
       objScript.CopyFile strTempDB, strPathToMDB, True
       '
       ' We are finished with TempDB.  Kill it.
       objScript.DeleteFile strTempDB
   End If
   '
   ' Always remember to clean up after yourself
   Set objAccess = Nothing
   Set objScript = Nothing
   '    
   ' ******************  END CODE HERE  ' ******************

dev history

I have tried several times now to make a batch file to compact-and-repair a .mdb so we can automatically do this regularly to the ccmdb_data.mdb files.

Turns out opening a password protected .mdb from the command line is not possible:

• http://support.microsoft.com/kb/292302
• http://support.microsoft.com/kb/207891

Will need to find a different way of doing this. Likely a script, but once a .mdb is opened by another one, it can't be compacted. Will need to look further.