How can I compile and restore the ACCESS 2007 database using .NET code?

Question

I need to compress and restore an Access 2007..accdb database file. I know that JRO.JetEngine can do this with .mdb files, but I need to restore the new version 2007 format from the code.

Any suggestions?

EDIT:

Here's what: I found that I can use the COM object library "Microsoft Database 12 Access Database Engine" and use the DBEngine class and call its CompactDatabse method. But it does not seem to me that I can provide a password for the database; It seems that the Office 12 Database Engine has no documentation anywhere. I found documentation for older versions of the CompactDatabase method, but that doesn't help me at all.

It drives me crazy.

Answer 1

It seems that the Office Database Database Database does not have any documentation anywhere.

Not quite right. Limited information available.

On MSDN, see

Access 2007 Developer Reference

There is a branch for Microsoft Jet Replication Object Link (JRO) , so JRO (one of the libraries containing ADO classic ) is still officially supported (and AFAIK still works) for ACE, for example accdb format. The CompactDatabase method takes two parameters, both of which are an OLE DB connection string. Therefore, providing a database password should not be different from a regular connection, for example. using Jet OLEDB:Database Password in the connection string. Remember that for accdb format you need to include Jet OLEDB:Engine Type=5 in the connection string, for example.

 Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyDB.accdb; Jet OLEDB:Database Password=MYPASSWORD; Jet OLEDB:Engine Type=5 

For what it's worth (not as much as it turns out), limited documentation for the ACE engine is in Office Help for Access 2007 .

Remember that most of the Database Access engine was developed in the 1990s when proprietary formats were furious; I suspect that the documentation was suppressed for commercial reasons, and that knowledge was simply lost. Thus, in the documentation currently available there are many cells, and they are large. Often the only way to find out how an engine works is to discover it through actual use. The documentation that exists contains some terrible mistakes: today I published several examples on SO where you can potentially use dummy functions ( LIMIT TO nn ROWS , CREATE TEMPORARY TABLE , etc.) are announced in Access help. Caution emptor.

Answer 2

I know this is an old thread, but it worked for me (for VB.Net). Hope this can help someone along the way:

Add a link to "Microsoft Office 12.0 Database Database Object Library"

 Dim dbe As New Microsoft.Office.Interop.Access.Dao.DBEngine dbe.CompactDatabase("C:\folder\database.accdb", "C:\folder\database_New.accdb", , , ";pwd=<database password>") 

http://www.tolchin.net/KB/Lists/Posts/Post.aspx?ID=9

Answer 3

Just FYI regarding JRO, it does not support files of Access version 2007 or higher. Although the CompactDatabase method in this library functions using the ACE OLEDB provider, it will actually generate a 2002-2003 database file (Jet 4.0).

BTW, engine type = 5 is Jet 4.0, so it must be a dead searchable version.

Answer 4

 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Text; using System.IO; using System.Data; using Microsoft.Office.Interop.Access.Dao; protected void btnSubmitDenim_Click(object sender, EventArgs e) { try { string oldFileName = HttpContext.Current.Server.MapPath(@"~/App_Data/MyDatainAccess2010.accdb"); string newFileName = HttpContext.Current.Server.MapPath(@"~/App_Data/Temp.accdb"); // Obtain a reference to the Access 2010 introp DBEngine formally known as JetEngine DBEngine engine = (DBEngine)HttpContext.Current.Server.CreateObject("Dao.DBEngine.120"); // Compact the database (saves the compacted version to newFileName) engine.CompactDatabase(oldFileName, newFileName); // Delete the original database File.Delete(oldFileName); // Move (rename) the temporary compacted database to // the original filename File.Move(newFileName, oldFileName); // The operation was successful lblResult.Text = "Database Compact completed"; } catch { // We encountered an error lblResult.Text = "Process Failed"; } } // Hope that helps // Wasif 

Answer 5

You do not need a JRO. JRO does not even exist - this is one of the ugly step-daughters of ADODB, which arose due to Microsoft's erroneous attempt to replace DAO with access / Jet / ACE with ADO, a non-native level of abstraction. JRO supports support for Jet-specific features not available in ADODB.

If you used DAO, you already have all the features, and it will work for all formats supported by the Access version that you are using (since the DAO version is synchronized with your Access version, when the db engine is improved, a new version of DAO is introduced there).

So, in A2007, you simply use the compact DAO methods (the restore operation was not performed as a separate command with Access 2, and the repair occurs only if the database engine determines during the CD that repair is necessary), if you are working from the outside Access, you need to use the ACA-compatible version of DAO. If you did not install A2007 and did not install ACE yourself, you will have this version of DAO installed along with it.