Click here to Skip to main content
Click here to Skip to main content

How to Compact Microsoft Access Database Using Visual Studio.NET

, 8 Jun 2006
Rate this:
Please Sign up or sign in to vote.
A way to automatically compact Microsoft Access database

Introduction

When you use Microsoft Access database, you may have noticed that file size always keeps growing and you can never decrease the size. Even if you delete some entries and tables, the size remains the size or sometimes grows despite the deletion. You then need to compact your database. Using the compact utility, the file size decreases to a large extent.

Background

Microsoft Access database creates this situation when you use an open connection very frequently or open the connection several times to communicate with the database. The very definite reason of size increasing is that your database is really of large size and is increasing. All three situations can be handled using the compact utility. In my previous article, I describe how to create and populate a Microsoft Access file. While doing this, I came to know that the file size is really so large and used the compact feature to reduce the size. It was an amazingly big difference, so then I decided to compact that automatically.

Requirements

For the development of this utility, you need to add a reference to the DOA Library. DOA library provides the functionality to compact the database. One more thing that may be interesting is the database file that you want to compact should be of large size, otherwise you will not feel the effect.

Code

VB

Imports System.IO
Module Module1
    Sub Main()
        Try
            Dim File_Path, compact_file As String
            'Original file path that u want to compact
            File_Path = AppDomain.CurrentDomain.BaseDirectory & "db.mdb"
            'compact file path, a temp file
            compact_file = AppDomain.CurrentDomain.BaseDirectory & "db1.mdb"
            'First check the file u want to compact exists or not
            If File.Exists(File_Path) Then
                Dim db As New DAO.DBEngine()
                'CompactDatabase has two parameters, creates a copy of 
                'compact DB at the Destination path
                db.CompactDatabase(File_Path, compact_file)
            End If
            'restore the original file from the compacted file
            If File.Exists(compact_file) Then
                File.Delete(File_Path)
                File.Move(compact_file, File_Path)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Module

C#

using System.IO; 
struct Module1 
{ 

 void Main() 
 { 
   try { 
     string File_Path; 
     string compact_file; 
     File_Path = AppDomain.CurrentDomain.BaseDirectory + "db.mdb"; 
     compact_file = AppDomain.CurrentDomain.BaseDirectory + "db1.mdb"; 
     if (File.Exists(File_Path)) { 
       DAO.DBEngine db = new DAO.DBEngine(); 
       db.CompactDatabase(File_Path, compact_file);  
     } 
     if (File.Exists(compact_file)) { 
       File.Delete(File_Path);
       File.Move(compact_file, File_Path); 
     } 
   } catch (Exception ex) { 
     MsgBox(ex.Message); 
   } 
 } 
}

Points of Interest

Using this utility, you can schedule a job to compact your database. But remember to schedule this at some specific time when you feel the database may not be in use as this compact utility deletes the original file and restores it with the compact one.

Summary

To use this utility, you do not need to do it yourself. Rather, you only need to schedule a job and relax. Happy programming.

History

  • 9th June, 2006: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Malik Nasir
Team Leader ZonG, China Mobile Company
Pakistan Pakistan
I am currently based in Islamabad,Pakistan. I have done my MS Computer Science and I am involved in Microsoft Visual Studio.net Windows/Web application development since 2003 untill that i have been working in VB, ASP and web scripting languages. Currently i am working in ZonG as Team LEad IT Projects and Planing. I am also the administrator of Business Process

Comments and Discussions

 
GeneralThank you very much Pinmemberramugaru9-Dec-06 7:54 
GeneralI've found a solution, here it is [modified] PinmemberMartijn Groen16-Oct-06 22:43 
GeneralAdditional note about adding the JRO object PinmemberMartijn Groen9-Dec-06 21:37 
Generalnice job PinmemberKali Babu12315-Jun-06 21:24 
QuestionIs Interop.DAO.dll part of VS2005 Pinmember*chopper*13-Jun-06 21:11 
AnswerRe: Is Interop.DAO.dll part of VS2005 PinmemberMalik Nasir13-Jun-06 23:29 
GeneralRe: Is Interop.DAO.dll part of VS2005 Pinmember*chopper*14-Jun-06 1:03 
GeneralRe: Is Interop.DAO.dll part of VS2005 [modified] PinmemberZeshan Sadiq19-Aug-07 16:06 
GeneralCheck PinmemberSteve Hansen8-Jun-06 22:26 
You might want to check if the compact file was created before deleting the original Smile | :)
GeneralRe: Check PinmemberMalik Nasir8-Jun-06 22:30 
GeneralRe: Check PinmemberDBuckner9-Jun-06 5:50 
GeneralRe: Check PinmemberBloodBaz12-Jun-06 2:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 9 Jun 2006
Article Copyright 2006 by Malik Nasir
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid