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 FilesCommon FilesSystemadomsado26.tlb"
no_namespace rename("EOF","adoEOF")
#import "C:Program FilesCommon FilesSystemadomsjro.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.”
