View source for Compact repair data.vbs

This could be run by a scheduled task or called otherwise... but not from inside Access because then the .mdb would still be open. 
So, it needs to become part of the news and backup.bat. Good thing we need to roll out a new one. 

We have a file [[Regional Server]]\Maintenance\compact_repair_data.vbs with the following code. I am putting the code here because it was a pain to find and I want to have a backup. :-)

    ' *****************  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"
            ' 
            ' 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. 

[[Category: Batch Files]]
[[Category:IT Instructions]]