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

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds