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.

Overview

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()
{
  try
  {

    CString csSourceConnection;
    CString csDestConnection;

    CoInitialize(0);

    UpdateData(TRUE);

    csSourceConnection.Format("Provider=Microsoft.Jet.OLEDB.4.0;
                               Data Source=%s;
                               Jet OLEDB:Database password=%s",
                               m_csSourcePath,m_csPassWd);

    if(m_csSourcePath.IsEmpty())
    {
      MessageBox("Please provide valid access data source path",
                 "Compact Access Database",MB_ICONINFORMATION);
      GetDlgItem(IDC_ED_SOURCE_PATH)->SetFocus();
      return;
    }
    if(m_bIsUseSource)
    {
      csDestConnection.Format("Provider=Microsoft.Jet.OLEDB.4.0;
                               Data Source=%s;Jet OLEDB:Database
                               password=%s",m_csSourcePath,
                                            m_csPassWd);
    }
    else
    {
      if(m_csDestination.IsEmpty())
      {
        MessageBox("Please provide valid access data destination
                    path","Compact Access Database",
                    MB_ICONINFORMATION);
        GetDlgItem(IDC_ED_DESTINATION)->SetFocus();
        return;
      }
      csDestConnection.Format("Provider=Microsoft.Jet.OLEDB.4.0;
                               Data Source=%s;Jet OLEDB:Database
                               password=%s",m_csDestination,
                                            m_csPassWd);
    }


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

    BeginWaitCursor();


jet->CompactDatabase(csSourceConnection.AllocSysString(),
                     csDestConnection.AllocSysString());
    EndWaitCursor();
    CoUninitialize();
  }
  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."

Downloads

Download demo project - 12 Kb
Download source - 64 Kb


Comments

  • 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.

    Reply
  • Thanks

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

    Originally posted by: Access Guy

    I can use this. Thanks alot!

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

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • CentreCorp is a fully integrated and diversified property management and real estate service company, specializing in the "shopping center" segment, and is one of the premier retail service providers in North America. Company executives travel a great deal, carrying a number of traveling laptops with critical current business data, and no easy way to back up to the network outside the office. Read this case study to learn how CentreCorp implemented a suite of business continuity services that included …

Most Popular Programming Stories

More for Developers

RSS Feeds