Compact an Access Database with VBS Script

Tuesday Oct 3rd 2000 by Danny Lesandrini

How to compact an Access Database using VBS Script from Danny Lesandrini.

The Problem with Compacting

If you have worked with Microsoft Access databases for any length of time, you have no doubt noticed that the mdb file itself tends to bloat as you work with it. Every time you open a form or report in design mode, the mdb file grows. Each time you add records to a table or create a temporary table, the mdb file grows. And contrary to what you might expect, closing or deleting objects and deleting records does not "shrink" the database. Those actions merely make shrinking the mdb file possible. The actual shrinking is done by executing a DAO (Data Access Objects) Method named CompactDatabase.

The CompactDatabase method may be executed from within the MS Access environment by selecting Compact Database from the Tools >> Database Utilities menu (in Access 97). This command will compact the current database. If you want to compact an external database, such as a "back end" database containing the tables, you could execute a line of code like this:
    Application.DBEngine.CompactDatabase "C:\Data.mdb", "C:\DataComp.mdb"
This line of code works fine and does it's job ... with one condition: Microsoft Access must be running for it to work.

This may not always be possible or convenient. What if you want to compact the mdb file at night when no users are attached to the data? You could leave an Access application running with a form open and put some complicated code in the Form_Timer event that would execute our one line of code. This seems excessive to me and is prone to unexpected errors.
  • You have to leave an Access application running all night! ... every night!
  • What if a well-meaning user turns off the application?
  • Is ther any chance that the computer might get recycled?
  • Is that a good use of your valuable resources?

Alternatively, it has been suggested that you could schedule a Microsoft Access application to open at a predefined time using the Windows Scheduler (or similar tool) and place the CompactDatabase code in the AutoExec macro. This will work and is a considerable improvement over the previously described solution, but if you're going to schedule this as a job anyway, why not move the work from a resource intensive Access application to a simple Windows Scripting Host, VBS script?

VBS ... a better solution

From VBS you can use COM Automation to exploit the MS Access Object Model and call the CompactDatabase method. This is done by using the CreateObject method to set an object variable to the MS Access Application object. The code sample includes the syntax for instantiating either Access 97 or Access 2000 application objects.

In order to use the sample, you need only change the hard-coded name of the database which is stored in the variable named strPathToMDB, to the name and path of the database you wish to compact. Paste the attached script into notepad and save as CompactMDB.vbs or with some other name which is meaningful to you. Execute the script by double-clicking the vbs file. Check to see that the newly compacted file exists, along with a copy of the original mdb file renamed to Filename.mdbx for safe keeping.

Now that your script is created and tested, it may be added to the Windows scheduler as a job to run nightly, or as often as is necessary and/or practical. The data file can be compacted and backups made automatically with a simple script that is easy to maintain.

View VBS Code

See All Articles by Columnist Danny Lesandrini

VBS Script to Compact Access Database

<-- Back To Article
    ' *****************  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:\EMSEnt.mdb"
    ' ////////////////////////////////////////////////////////////////

    ' Set a name and path for a temporary mdb file
     strTempDB = "C:\Comp0001.mdb"

    ' Create Access 97 Application Object
    Set objAccess = CreateObject("Access.Application.8")

    ' For Access 2000, use Application.9
    'Set objAccess = CreateObject("Access.Application.9")

    ' 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

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

Back To Article

See All Articles by Columnist Danny Lesandrini

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved