Compact an Access Database Programmatically

Environment: The user has installed Jet Database engine at least, VC 6, Windows 2000

It takes time to find a better way to compact an Access database programmatically. What made me do this is that I encountered a problem with increasing an Access database's size, which in turn affects my application's performance. I tried deleting old, unwanted records but I still have the same problem. Now, have a look at what our databases, such as Access and FoxPro, are doing.

When we insert/delete records continually, an Access database will only grow. This happens because the database always extends when new insertions occur. It never writes a new record to the space that was allocated by a previous insertion, even if those previously inserted records are currently deleted. So, if you have a database that's 1 Mb size, for example (let's say 1,000 records), delete them all, and then insert another 1,000 records, your database will be about 2 Mb.

So, the simplest way to achieve better performance and to decrease the size of an Access database is to periodically compact the database.


To compact a database, it's not necessary to install the MS Access application. Jet Engine 3.5 or later is enough to compact the Access database. To compact a database, I import two files:

#import "C:\Program Files\Common Files\System\ado\msado26.tlb"
            no_namespace rename("EOF","adoEOF")
#import "C:\Program Files\Common Files\System\ado\msjro.dll"

Thanks go to Mr. Roy Fine for his knowledge. Then, create an object of IJetEnginePtr:

JRO::IJetEnginePtr jet(__uuidof(JRO::JetEngine));

Then, make a call to the Compactdatabase method for the Jet interface we created earlier:

jet->CompactDatabase("Source Connection" ,
                     "Destination Connection");

Source connection Syntax:" Provider=Microsoft.Jet.OLEDB.4.0;
       Data Source=C:\\Program Files\\FastTalk3000\\ft3000.mdb"

We can keep the destination connection as the source itself, or we can provide a new mdb file to compact the source.

Here is the code snippet:

void CCompactMDBDlg::OnButCompact()

    CString csSourceConnection;
    CString csDestConnection;



                               Data Source=%s;
                               Jet OLEDB:Database password=%s",

      MessageBox("Please provide valid access data source path",
                 "Compact Access Database",MB_ICONINFORMATION);
                               Data Source=%s;Jet OLEDB:Database
        MessageBox("Please provide valid access data destination
                    path","Compact Access Database",
                               Data Source=%s;Jet OLEDB:Database

    JRO::IJetEnginePtr jet(__uuidof(JRO::JetEngine));


  catch(_com_error &e)
    CString csError;
    csError =(LPCTSTR) e.Description();
    MessageBox(csError,"Error Info",MB_ICONEXCLAMATION);


Note: You also can compact the database through Access's Tools Menu->Database utilities->Compact and Repair Database. Command line parameters to the Access file/compact target database or target Access project compact the Access project that was specified before the /compact option, and then closes Microsoft Access.

If you omit a target file name following the /compact option, the file is compacted to the original name and folder. To compact to a different name, specify a target file. If you don't include a path in target database or target Access project, the target file is created in your My Documents folder by default.

I never tried this method, just got information so shared; for more info in using the startup command line in Access, please refer to MS Access Help ?? or Search in MSA ccess Help with the key phrase "Startup command-line options."


Download demo project - 12 Kb
Download source - 64 Kb


  • Common source and destination

    Posted by Legacy on 11/25/2003 12:00am

    Originally posted by: jayph

    Does this work if source and destionation path are same, the code in the microsoft web site does not work.

  • Thanks

    Posted by Legacy on 08/19/2003 12:00am

    Originally posted by: Access Guy

    I can use this. Thanks alot!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Moving from an on-premises environment to Office 365 does not remove the need to plan for disruptions or reduce the business risk requirements for protecting email services. If anything, some risks increase with a move to the cloud. Read how to ease the transition every business faces if considering or already migrating to cloud email. This white paper discusses: Setting expectations when migrating to Office 365 Understanding the implications of relying solely on Exchange Online security Necessary archiving …

  • Enterprises are increasingly looking to platform as a service (PaaS) to lower their costs and speed their time to market for new applications. Developing, deploying, and managing applications in the cloud eliminates the time and expense of managing a physical infrastructure to support them. PaaS offerings must deliver additional long-term benefits, such as a lower total cost of ownership (TCO), rapid scalability, and ease of integration, all while providing robust security and availability. This report …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date