24 December 2009

How to Compact Access Database Programmatically

Add a reference of “Microsoft Jet and Replication Objects 2.6 Library” in your project.



Now write the following function to compact the Access database with .mdb extension.

C#

private void CompactDatabase()
{

   //Making object of the JRO.JetEngine
   JRO.JetEngine objJetEngine = new JRO.JetEngine();

   //Specifying the connection strings for the Source and Target databases

   string SourceDBConnectionString = "Data Source=\"" + "SourceDBPath" + "\";Jet OLEDB:Database Password=" + "Password";

   string TargetDBConnectionString = "Data Source=\"" + "destFilePath" + "\";Jet OLEDB:Database Password=" + "Password";

   //Calling the CompactDatabase function of the JRO.JetEngine object
   objJetEngine.CompactDatabase(SourceDBConnectionString, TargetDBConnectionString);

}

VB.Net

Private Sub CompactDatabase()
   'Making object of the JRO.JetEngine
   Dim objJetEngine As New JRO.JetEngine()

   'Specifying the connection strings for the Source and Target databases

   Dim SourceDBConnectionString As String = "Data Source=""" & "SourceDBPath" & """;Jet OLEDB:Database Password=" & "Password"

   Dim TargetDBConnectionString As String = "Data Source=""" & "destFilePath" & """;Jet OLEDB:Database Password=" & "Password"

   'Calling the CompactDatabase function of the JRO.JetEngine object
   objJetEngine.CompactDatabase(SourceDBConnectionString, TargetDBConnectionString)

End Sub

The above code will compact only the Access databases of 2003 and earlier versions, (with .mdb extensions). If you want to compact the Access databases 2007 version, please visit the following post:-
http://techieyogi.blogspot.com/2009/11/how-to-compact-access-2007-database.html





 

1 comment: