You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

How To Compact Microsoft Access Database Through ADO

Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Summary
Although ADO specification does not provide objects to compact Microsoft Access databases, this capability can be achieved by using the ADO extension: Microsoft Jet OLE DB Provider and Replication Objects (JRO). This capability was implemented for the first time in the JET OLE DB Provider version 4.0 (Msjetoledb40.dll) and JRO version 2.1 (Msjro.dll). These DLL files are available after the install of MDAC 2.1. You can download the latest version of MDAC from the following Web site:
More information
To compact an Access database using ADO, MDAC 2.1 or later must be properly installed on the computer. The following are the main steps to compact a Microsoft Access database using Visual Basic and Visual C++:

Visual Basic: Compacting an Access Database via ADO

  1. In the Visual Basic IDE, on the Projects menu, click References.
  2. Add Microsoft Jet and Replication Objects X.X library, where (X.X is greater than or equal to 2.1).
  3. Add the following lines of code to Visual Basic, replacing the data source and destination database paths if needed:
    Dim jro As jro.JetEngineSet jro = New jro.JetEnginejro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\nwind2.mdb;Jet OLEDB:Database Password=test", _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\abbc2.mdb;Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test"					
NOTE: In DAO 3.60, the RepairDatabase method is no longer available. The above code shows the use of the CompactDatabase method. This method replaces the DAO 3.5 RepairDatabase method.

Visual C++: Compacting an Access Database via ADO

  1. Besides #import, the Msado15.dll (MDAC2.1), add the following #import statement to generate the wrapper classes for JRO to your .cpp classes (alternatively, you can generate the wrapper classes more efficiently by using the no_implementation and implementation_only attributes of the #import pre-processor statement):
    #import "C:\PROGRAM FILES\COMMON FILES\System\ado\MSJRO.DLL" no_namespace 					
  2. Add the following (specifying your own source and destination database paths) to the .cpp file where you want to compact the database:
    ...try{   IJetEnginePtr jet(__uuidof(JetEngine));  jet->CompactDatabase( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\nwind2.mdb;Jet OLEDB:Database Password=test", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\abbc.mdb;" \ "Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test");}catch(_com_error &e) {          ::MessageBox(NULL, (LPCTSTR)e.Description( ), "", MB_OK) ;    }					
NOTE: The Jet OLEDB:Engine Type=4 is only for Jet 3.x format MDB files. If this value is left out, the database is automatically upgraded to the 4.0 version (Jet OLEDB:Engine Type=5). See the following table for appropriate values for Jet OLEDB:Engine Type:
Jet OLEDB:Engine TypeJet x.x Format MDB Files
1JET10
2JET11
3JET2X
4JET3X
5JET4X
References
For additional information, please see the followingarticle in the Microsoft Knowledge Base:
230496 Compacting Microsoft Access database via OLE DB
CompactDatabase RepairDatabase reindex
Properties

Article ID: 230501 - Last Review: 05/27/2014 02:47:00 - Revision: 5.0

  • Microsoft ActiveX Data Objects 2.7
  • kbhowto kbjet kbprovider KB230501
Feedback
ript' src='" + (window.location.protocol) + "//c.microsoft.com/ms.js'><\/script>");