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

Question

Hi all,

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

You don't need JRO. JRO should not even exist -- it is one of the ugly stepchildren of ADODB that came about because of Microsoft's misguided attempt to replace Access/Jet/ACE's native DAO with ADO, a non-native abstraction layer. JRO is there to provide support for Jet-specific functionality not available in ADODB.

If you used DAO instead, you'd have all the functionality right there already, and it works for all formats supported by the version of Access you're using (since the DAO version is synchronized with your Access version, that is, when the db engine is enhanced, there's a new version of DAO to go with it).

So, in A2007, you'd simply use the DAO compact methods (there's been no repair operation as a separate command since Access 2, and a repair happens only if the database engine determines during the compact that a repair is necessary). If you're working from outside Access, then you need to use the ACE-compatible version of DAO. If you do not have A2007 installed and have installed the ACE independently, you have that version of DAO installed along with it.

Answer 2

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.