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

  • Organizations are increasingly gravitating toward mobile-first application development as they assess the need to revamp their application portfolios to support touch computing and mobility. Consumerization has brought higher expectations for application usability along with the mobile devices themselves. Enterprises are increasingly shifting their new application acquisitions and development efforts toward mobile platforms. With this backdrop, it is natural to expect application platform vendors to invest in …

  • Thanks to wide spread cloud hosting and innovations small businesses can meet and exceed the legacy systems of goliath corporations. Explore the freedom to work how you want, with a phone system that will adapt to your evolving needs and actually save you lots of expense—read Get an Enterprise Phone System without High Cost and Complexity. The article clearly illustrates: The only hardware you'll need is phone equipment for advanced voice and fax. How to join all your employees, mobile devices, …

Most Popular Programming Stories

More for Developers

RSS Feeds

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