How do I compact and repair an ACCESS 2007 database by .NET code?

Question

I need to compact and repair an Access 2007 .accdb database file. I know that JRO.JetEngine can do this with .mdb files, but I need to repair the newer version 2007 format by code.

Any suggestions?

EDIT:

Here is the thing: I found that I can use the COM object library "Microsoft Office 12 Access Database Engine Object Library" and use the DBEngine class and call its CompactDatabse method. But there doesn't seem to be a place for me to provide the database password; Seems like Office 12 Database Engine doesn't have any documentation anywhere. I found some documentation for older versions of CompactDatabase method but those don't help me at all.

This is driving me crazy.

Answer 1

Seems like Office 12 Database Engine doesn't have any documentation anywhere.

Not quite true. There is limited information available.

On MSDN, see:

Access 2007 Developer Reference

There is a branch for Microsoft Jet Replication Objects (JRO) Reference, so JRO (one of the libraries comprising ADO classic) is still officially supported (and AFAIK does still work) for ACE e.g. the accdb format. The CompactDatabase method takes two parameters, both of which is an OLE DB connection string. Therefore, supplying the database password should be no different from a regular connection e.g. using Jet OLEDB:Database Password in the connection string. Remember, for accdb format you need to include Jet OLEDB:Engine Type=5 in the connection string e.g.

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 much, as it turns out), limited documentation for the ACE engine is found in the Office Help for Access 2007.

Bear in mind, most of the Access Database Engine was developed during the 1990s when proprietary formats were all the rage; I suspect documentation was suppressed for commercial reasons and the knowledge is simply lost. So there are many holes in the currently-available documentation, and large ones at that. Often, the only way of finding out how the engine works is to discover it through actual usage. The documentation that does exist contains some appalling errors: already today I've posted a couple of examples on SO where potentially use yet fictitious functionality (LIMIT TO nn ROWS, CREATE TEMPORARY TABLE, etc) has been advertised in the Access Help. Caveat emptor.