How To Compacting Microsoft Access Database Through OLE DB

Article translations Article translations
Article ID: 230496 - View products that this article applies to.
This article was previously published under Q230496
Expand all | Collapse all

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

}

				

NOTE:
  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.

REFERENCES

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

Properties

Article ID: 230496 - Last Review: July 15, 2004 - Revision: 4.3
APPLIES TO
  • 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
Keywords: 
kbhowto kbjet kbprovider KB230496

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com