HOWTO: Compacting Microsoft Access Database Through OLE DB

ID: Q230496

The information in this article applies to:

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 which can be obtained separately from Microsoft (Please see knowledge base article Q192328).


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.


The following code demonstrates the above steps using Visual C++ ATL OLE DB consumer templates:

#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;

}

NOTE:
  1. If you have a compiler error such as:
    DBPROP_JETOLEDB_ENGINE undefined specifier
    Please contact Microsoft Support for a revised version of Msjetoledb.h.


  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

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

Last Reviewed: June 2, 1999