Is there VBA code equivalent of the Compact and Repair Database action using Access 2003?

Posted on 2012-08-24
Last Modified: 2012-08-24
I am developing an application in Access 2003. I realize that I can go to the menu item
TOOLS -> Database Utilities -> Compact and Repair Database to compress the size of my application. Is there a way I can accomplish this task by setting up a command button to click. And as a result of the click event, write VBA code that would accomplish the compression.

In other words, is there VBA code equivalent of the Compact and Repair Database action?
Or do you have to manually perform this action?
Question by:zimmer9
    LVL 22

    Expert Comment

    by:Kelvin Sparks

    Author Comment

    Okay, I see the following code.
    Is there a way to compress to the same filename?

    'Try this code. It will compress a .mdb

    Function CompactMyDB()

    'File name
    Const ConFileName = "Cashbook Tables.mdb"
    'Path name to database folder
    Const ConFileFolder = "C:\Program Files\Database\"
    'Path name to backup folder
    Const ConBackUp = "A:\"

    'Compress to a A Drive
    DBEngine.CompactDatabase ConFileFolder & ConFileName, ConBackUp & ConFileName
    LVL 75

    Accepted Solution

    You can't really compact the open db you are in:

    "Copies and compacts a closed database,"
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    And there is Michael Kaplan's utility:

    TSI SOON (Shut One, Open New) database add-in
    LVL 30

    Expert Comment

    I am using Access 2010, it gave me this message when issuing
      RunCommand acCmdCompactDatabase

    Open in new window

    "You cannot compact the open database by running a macro or Visual Basic code.
    Instead of using a macro or code, dick the File tab and then dick Compact and Repair Database."

    But there should be a way, issuing commands to Ribbon. Didn't practice with that yet.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now