You are currently offline, waiting for your internet to reconnect

How To Compacting Microsoft Access Database Through OLE DB

This article was previously published under Q230496
This article has been archived. It is offered "as is" and will no longer be updated.
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).
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 earlier 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;}				

  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:
    228525 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.
For additional information, please click the article number below to view the article in the Microsoft Knowledge Base:
230501 How To Compacting Microsoft Access Database via ADO
CompactDatabase RepairDatabase reindex

Article ID: 230496 - Last Review: 12/05/2015 14:21:46 - Revision: 4.3

Microsoft OLE DB Provider for Jet 4.0, Microsoft Data Access Components 2.1, Microsoft Data Access Components 2.5, Microsoft Data Access Components 2.6

  • kbnosurvey kbarchive kbhowto kbjet kbprovider KB230496