23 November 2009

How to Compact Access 2007 Database Programmatically

Add a reference to “Microsoft Office 12.0 Access Database Engine Object Library”.



And write a following code to compact the Access 2007 Database:-

// Create a new object of DBEngine

Microsoft.Office.Interop.Access.Dao.DBEngine objDbEngine = new Microsoft.Office.Interop.Access.Dao.DBEngine();

// Call the CompactDatabase method on object of DBEngine

objDbEngine.CompactDatabase(“C:\SourceDB.accdb”, “C:\TargetDB.accdb”, null, null, ";pwd=passwordOfDatabaseIfAnyElseNULL");

For more information about the function parameters refer to the following link:-

http://msdn.microsoft.com/en-us/library/bb220986.aspx

15 comments:

  1. Above code of CompactDatabase will do the compact for .MDB file? or is it only for .accdb. I tried using .mdb but its giving error. Please let me know if we can you this code for .mdb using Microsoft.Office.Interop.Access.Dao.DBEngine objDbEngine = new Microsoft.Office.Interop.Access.Dao.DBEngine(); ?

    ReplyDelete
  2. The above code will work only for the Access 2007 format, that is .accdb. To format the .mdb files you have to use the JRO object. The following link would be certainly of your help - http://www.codeproject.com/KB/database/CompactAndRepair.aspx

    ReplyDelete
  3. Thanks so much for this...

    ReplyDelete
  4. Yogi:
    Nice. You helped me. Thank you!!!
    José.

    ReplyDelete
  5. Thanks for the code. Should the target computer have Microsoft Access 2007 installed? or Which file I have to redistribute?

    ReplyDelete
  6. It is not mandatory to have Microsoft Access 2007 installed on the target computer. But in that case you need to have AccessDatabaseEngine installed, in order to get your application working. You may refer my earlier post http://techieyogi.blogspot.com/2009/11/installing-access-database-engine-with.html on detailed instructions.

    ReplyDelete
  7. Thanks, helped me fix some Access weirdness!

    ReplyDelete
  8. That's a really helpful post, thanks. Works a treat for me and my customer.

    ReplyDelete
  9. Hello sir,

    I have completed an access database. There is no problem for me to connect this database and show data. Please tell me how to connect MS Access form in asp.net with VB and run all of macros ??????What namespace to imports and how to use the object or classes? The access form(s) will show when I click button and it will run access macros.

    Annie

    ReplyDelete
    Replies
    1. Hi Annie
      You can connect to access database using ado.net, and use it as your data source with connection string given here - "https://www.connectionstrings.com/access/".
      However I don't think running macros will be possible from asp.net.
      You need to use access file only for this purpose because this is how it is designed, as an application with GUI, macros, forms etc, and not just database source.
      Thanks.

      Delete
  10. I need code for compact the MS access database 2002 or 2003 using VB6

    ReplyDelete
  11. You can try to use third party Access file recovery software to recover your lost and corrupted data from .mdb files. For more details about this software:- http://www.recoverydeletedfiles.com/access-file-recovery-tool.html
    /"

    ReplyDelete
  12. Access file recovery software is an affordable or efficetive tool. It can easily repair and recover from damaged MDB/ACCDB files without any hassles. Reference link:- http://www.recoverfilesdownload.com/access-file-recovery.html
    The software also available free trial demo version :

    ReplyDelete
  13. Access File Recovery Tool that easily repairs corrupt MS Access (.MDB or .ACCDB) database file and then recover tables, queries, forms, macros, reports, etc. It also restores permanently deleted data from MDB file of MS Access 2013, 2010, 2007, 2003, 2002, 2000 on your latest Windows 8.1 PC & all below Windows version like as Win8, Win7, Vista, 2003 and XP.

    Download:- http://www.filesrecoverytool.com/access-file-recovery.html

    ReplyDelete
  14. I would like to suggest this application, which will repair your access data in trial version and see you the preview of recover data. http://www.recoverfilesdownload.com/access-file-recovery.html

    ReplyDelete