Open a Database with any version of Access from the  Right Click context menu

Add a Compact Database Option to your Right Click Menu.

How to Automatically compact your Access dbs on a scheduled basis.


More to Come…. .

To add the option via Windows Explorer, go to the folder options from the menu and select the file type tab.  

1.) Find the MDB (mda, mde) file type and click "Advanced"

2.) Click the New button and add the name of the command in the Action option you want to show up in the context menu such as "Compact".

3.) Browse to the location of msaccess.exe

4.) After the path has been entered in the Application option add the following "%1" /compact

If you want to manually edit the registry on your computer in Windows 95/98 and NT 4/2000:

1.) Make sure you have a back up copy of your registry, in case of an error.

2.) Open the Registry Editor, and under the key "Hkey_Classes_Root", find the key "Access.Application.X". Where X = the highest version of Access you have installed.

3.) Under this Access.Application.X key click on the "Shell" Key. While Shell is highlighted, use the edit menu item to Add . NewKey. Call this new key "Compact".

4.) Highlight the new key and repeat the process in item 3 to add another new key below "Compact" and call this key "Command".

5.) Click on the new "Default" entry in the "Command" key and the key value dialog will be displayed. Enter the path to the version of Access used for this compact method on the computer, appending a "%1" at the end, and the adding the command line switch "/compact". Example: "C:\program files\Microsoft Office\msaccess.exe" "%1" /compact".

The sample script performs routine maintenance on application that has a temporary data db and a standard data db.  In this script, all data from the tables in a temporary db is deleted and then the db is compacted, then the script moves on and  compacts the backend db of the application.   This type of script can be run nightly on a file server.   You can write a similar script to instantiate an instance of Access itself, open a database and run a report.

Note that this script instantiates the DAO engine 3.51, used in Access 97 to do the compacting.  This reference should be changed if you are using Access 2000 to DAO 3.6.  In addition, this script is designed to be run by the WSH console executable cscript.exe, not by the Windows version of WSH (wscript), since it echoes various information to the dos box window it would run in.  

    'CompactDbs.vbs
    'Windows Scripting Host Script, for Win 95/98/NT/2000
    'Compatible with WSH Version 1 and 2
    'Written By ACG Soft, Ann Arbor, MI
    'Run from the command line using the cscript runtime engine
    'Do not run from the Windows interface unless all the 
    'Echos are deleted
    '-----------------------------------------------------

    Dim objEngine
    Dim objDB
    Dim strDb1, strDb1a
    Dim FSO
 
    WScript.Echo "**************************"
    Wscript.echo "Compacting Db Applications"
    WScript.Echo "-DO NOT CLOSE THIS WINDOW-"
    WScript.Echo "-It will close when done-"
    WScript.Echo "**************************"
    WScript.Echo ""

    strDb1 = "c:\TargetApp\TempData.mdb"   
    strDb1a = "c:\TargetApp\BackEndData.mdb"

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objEngine = WScript.CreateObject("DAO.DBEngine.35")

    call CompactTemp(FSO, objEngine, strDb1, "Temp Data")
    call CompactDB(FSO, objEngine, strDB1a, "Live Backend Data")

    WScript.Echo ""
    WScript.Echo "*******************"
    WScript.Echo "All Jobs completed"
    WScript.Echo "*******************"
   
Function CompactTemp(objFSO, objEngine, strDb1, strDbName)
   'Deletes temp data and compacts the db.
   On Error Resume Next
   strdbtemp = Left(strDb1, len(strDb1)-3) & "tmp"
   Wscript.echo "Now Compacting " & strDbName
   If FSO.FileExists(strDb1) = False then 
      WScript.Echo "The database file " & strDb1 & " could not be found."
      Exit Function
   End if

   Set objDb = objEngine.Opendatabase(strDB1)
   With objDb
      For Each dbTableDef In .TableDefs
         strTblName = dbTableDef.Name
         If InStr(strTblName, "tmp") <> 0 Then 
             .Execute ("Delete * FROM " & strTblName)
         End If
      Next
   End With
   objDb.close
   'compact temp
   objEngine.CompactDatabase strDb1, strdbtemp
   If Err <> 0 Then Exit Function
   fso.deletefile strdb1
   fso.copyfile strdbtemp, strdb1
   fso.deletefile strdbtemp

End Function
    

Function CompactDB (objFSO, objEngine, strDb1a, strDbName)
   'compact db
   WScript.Echo "Now Compacting " & strDbName
   if FSO.FileExists(strDb1a) = False then 
      WScript.Echo "The database file " & strDb1a & " could not be found."
      Exit Function
   End If

   strdbtemp = Left(strDb1a, len(strDb1a)-3) & "tmp"
   objEngine.CompactDatabase strDb1a, strdbtemp
   If Err <> 0 Then Exit Function
   fso.deletefile strdb1a
   fso.copyfile strdbtemp, strdb1a
   fso.deletefile strdbtemp
End Function