Compact MDB from Excal VBA

Posted on 2007-10-03
Last Modified: 2013-11-27
I have an Excel VBA routine that updates an Access MDB. How do I perform a compact/repair after the update (from Excel using VBA) so I don't leave the file all bloaty?
Question by:Wicklunda
    LVL 4

    Expert Comment

    LVL 34

    Expert Comment

    easiest way is to set the option to compact on close. And simply open and close the database from Excel.

    LVL 56

    Accepted Solution

     There are quite a few ways to do that.  Here's one routine to compact using JRO (Jet Replication Objects), which is part of ADO:

    Public Sub CompactDB()
      'Microsoft Jet and Replication objects
      Dim objJE As New JRO.JetEngine
      Dim strSource As String
      Dim strTarget As String

      strSource = " "
      strTarget = " "
      objJE.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTarget & ";Jet OLEDB:Engine Type=4;"

    End Sub

    Engine types are: Access 97 = 4, Access 2000 and up = 5

      There is also a method via DAO (Data Access Objects) and also by starting up Access itself as an automation object and calling an undocumented SYSCMD() function.


    Author Comment

    Exactly what I was looking for!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    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…

    779 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

    11 Experts available now in Live!

    Get 1:1 Help Now