HOWTO: Compacting Microsoft Access Database Through OLE DB

• Microsoft OLE DB Provider for Jet, version 4.0

SUMMARY

OLE DB specification doesn't provide interfaces to compact or repair databases. However, the OLE DB Provider for Microsoft Jet version 4.0 exposes this functionality through a custom interface: IJetCompact (TDataSource cotype). IJetCompact is defined in the header file Jetoledb.h that can be obtained separately from Microsoft (Please see knowledge base article Q228525).

MORE INFORMATION

To repair and compact a Microsoft Access database using OLE DB, MDAC 2.1 or higher version must be properly installed on the computer. The following steps are required:

1. Create and initialize the data source objects by using interfaces of IDBInitialize and IDBCreateSession.



2. Use GetJetEngineType function to get the version of Jet database.



3. Fill a DBPROPSET structure with information about the destination database to compact to.



4. Query the IDBCreateSession object for the Jet Compact object: IJetCompact.



5. Call IJetCompact's Compact method passing in the DBPROPSET created in step 2.

#include <objbase.h>

#define DBINITCONSTANTS
#define INITGUID

#include <initguid.h>
#include <oledb.h>
#include "msjetoledb.h"
#include "jetoledb.h"	// for IJetCompact interface
#include <atldbcli.h>

long GetJetEngineType( LPCTSTR src );

class OLEINITIALIZE
{
	bool m_bOleInit;
public:
	OLEINITIALIZE()
	{
		m_bOleInit= (CoInitialize(NULL)==S_OK);		

	}
	~OLEINITIALIZE()
	{
		if (m_bOleInit)
		CoUninitialize();
	}
};

HRESULT CompactDatabase(LPCTSTR src, LPCTSTR dest)
{
	// Initialize environment must be the first line in your function
	OLEINITIALIZE oleinit;

	CDataSource ds;
	CComPtr<IJetCompact> spJetCompact =NULL;
	CComPtr<IDBCreateSession> spSession =NULL;
	HRESULT	hr=0;

	//Specify the source DSO
	ds.Open(CLSID_JETOLEDB_4_00, src);

	CDBPropSet propset1(DBPROPSET_DBINIT);
        propset1.AddProperty(DBPROP_INIT_DATASOURCE, dest);

	long x = GetJetEngineType( src );
	CDBPropSet propset2(DBPROPSET_JETOLEDB_DBINIT);
	propset2.AddProperty(DBPROP_JETOLEDB_ENGINE, x);

	CDBPropSet dbsets[2] = { propset1, propset2 };

	// Have we connected to the database?
	ATLASSERT(ds.m_spInit != NULL);
		hr = ds.m_spInit->QueryInterface(IID_IDBCreateSession, (void**)&spSession);
	if (FAILED(hr))
		return hr;

	//IJetCompact only supported in Jet 4.0 and above
	hr = spSession->QueryInterface( __uuidof(IJetCompact), (void**)&spJetCompact);
	if (FAILED(hr))
		return hr;

	//Delete the destination file if it exists
	remove(dest);

	//Ok compact
	//hr = spJetCompact->Compact(1, &propset);
	hr = spJetCompact->Compact(1, dbsets);

	if (FAILED(hr))
		return hr;
	
	return hr;

}

long GetJetEngineType( LPCTSTR src )
{
	HRESULT hr;
	CDataSource ds;
	CComBSTR bstrSource;
	VARIANT vPropValue;

	// Initialize our variant to VT_I4 and 0.
	vPropValue.vt   = VT_I4;
	vPropValue.lVal = 0L;

	// Exit now if source database is null.
	if( NULL == src ) return 0;

	// Build connection string for source.
	bstrSource = L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
	bstrSource += src;
	bstrSource += L";";

	hr = ds.OpenFromInitializationString( bstrSource );
	hr = ds.GetProperty( DBPROPSET_JETOLEDB_DBINIT, DBPROP_JETOLEDB_ENGINE, &vPropValue );<BR/>
<BR/>
// Version returned will be one of these values:
	// 
	// #define JETDBENGINETYPE_UNKNOWN	0x00
	// #define JETDBENGINETYPE_JET10	0x01
	// #define JETDBENGINETYPE_JET11	0x02
	// #define JETDBENGINETYPE_JET2X	0x03
	// #define JETDBENGINETYPE_JET3X	0x04
	// #define JETDBENGINETYPE_JET4X	0x05


	return vPropValue.lVal;

}
 

1. If you have a compiler error such as:

   DBPROP_JETOLEDB_ENGINE undefined specifier

   For additional information about updated Jet header files, please click the article number below to view the article in the Microsoft Knowledge Base:

   Q228525 PATCH: JetVC.exe VC++ Support Files for the Jet OLE DB Provider




2. Inline the GetJetEngineType function in your code if you don't want to open another connection.



3. Compacting a database also repairs the database, unlike in DAO where it was a separate functionality. In OLE DB there is no way to only repair a database.

REFERENCES

For additional information, please click the article number below to view the article in the Microsoft Knowledge Base:

Q230501 HOWTO: Compacting Microsoft Access Database via ADO

Additional query words: CompactDatabase RepairDatabase reindex

Keywords : kbADO kbJET kbOLEDB kbProvider kbGrpVCDB kbGrpMDAC
Version : WINDOWS:4.0
Platform : WINDOWS
Issue type : kbhowto