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:
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.