Microsoft KB Archive/230496

= How To Compacting Microsoft Access Database Through OLE DB =

Article ID: 230496

Article Last Modified on 7/15/2004

-

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

-



This article was previously published under Q230496



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:


 * 1) include 


 * 1) define DBINITCONSTANTS
 * 2) define INITGUID


 * 1) include 
 * 2) include 
 * 3) include "msjetoledb.h"
 * 4) include "jetoledb.h"  // for IJetCompact interface
 * 5) include 

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 spJetCompact =NULL; CComPtr 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 );  // 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:  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

 Inline the GetJetEngineType function in your code if you don't want to open another connection. 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.</li></ol>

<div class="references_section">