C# Compact and repair .accdb files using DAO or ADOX

Question

As stated here I'm rebuilding tables from SQL Server to Access by using C# Thanks to the help I received I could finish the process but since the .accdb files are pretty large, I need to compact and repair them afterwards. For that, I used the marked answer from here. Strangely, there was only a reference for "Microsoft Office 16.0 Access Database Engine Object Library" that I could add to my project.

using Microsoft.Office.Interop.Access.Dao;

var engine = new DBEngine(); // Exception
var destFile = Path.GetFileNameWithoutExtension(filepath) + "_Compact" + ".accdb";
var destFilePath = Path.Combine(Path.GetDirectoryName(filepath), destFile);

engine.CompactDatabase(filepath, destFilePath);

At the Initialization of the DBEngine - Object, an exception is thrown:

Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

Also, is there a way to use ADOX for this task, since I'm already using it for creating my catalogs?

Try using late binding to create the database engine object: `using System.Reflection; ` and `Object engine= Activator.CreateInstance(Type.GetTypeFromProgID("DAO.DBENGINE.36"));` — Erik A, Nov 17 '17 at 16:11
@ErikvonAsmuth thanks for your reply. How do I call the method "CompactDatabase", since the compiler states that there is no definition for that method? — Scorch, Nov 24 '17 at 12:58
Ah, you can work around that by using the `Dynamic` type (more of a vb guy where these things just work without it): `Dynamic engine= Activator.CreateInstance(Type.GetTypeFromProgID("DAO.DBENGIN‌E.36"));` — Erik A, Nov 24 '17 at 13:04
Thank you very much. Might be important for other people with the same problem: the argument for the method `GetTypeFromProgID()` is case-sensitive. `Dynamic engine= Activator.CreateInstance(Type.GetTypeFromProgID("DAO.DBEngine.36"));` did the trick for me. — Scorch, Nov 24 '17 at 13:21

Jericho Johnson · Answer 1 · 2017-11-22T17:23:11.077

Unfortunately, JRO, ADO, nor ADOX can be used to Compact and Repair a Microsoft Access .accdb (Access 2007 and above) database file. However, you are on the right track by using the DBEngine object. One method you can use to avoid relying on the PIA would be to use late binding on the ACE DAO engine (which replaced the JET DAO engine for the older .mdb format).

This method requires no PIA's or Project References. But it does require that the ACE Engine be installed on the machine. The ACE is freely distributable and can be downloaded from Microsoft - Microsoft Access Database Engine 2010 Redistributable

using System;

// Use ACE DAO to Compact an Access .ACCDB file
// This method uses late binding to create the ACE DAO.DBEngine object
public bool CompactDatabaseACE(string SourceDb, string TempPath)
{
    string Temp1Db, Temp2Db;
    object[] oParams;
    bool retVal = false;

    Temp1Db = Path.Combine(TempPath, Path.GetFileNameWithoutExtension(SourceDb) + ".cmp");
    Temp2Db = Path.Combine(Path.GetDirectoryName(SourceDb),Path.GetFileNameWithoutExtension(SourceDb) + ".old");
    if (File.Exists(Temp1Db))
        File.Delete(Temp1Db);
    if (File.Exists(Temp2Db))
        File.Delete(Temp2Db);
    oParams = new object[]
    {
        SourceDb, Temp1Db
    };
    try
    {
        object DBE = Activator.CreateInstance(Type.GetTypeFromProgID("DAO.DBEngine.120"));
        DBE.GetType().InvokeMember("CompactDatabase", System.Reflection.BindingFlags.InvokeMethod, null, DBE, oParams);
        if (File.Exists(Temp1Db))
        {
            try
            {
                File.Move(SourceDb, Temp2Db);
            }
            catch { }
            if (File.Exists(Temp2Db))
            {
                try
                {
                    File.Move(Temp1Db, SourceDb);
                }
                catch { }
                if (File.Exists(SourceDb))
                {
                    retVal = true;
                }
            }
            if (File.Exists(Temp1Db))
                File.Delete(Temp1Db);
            if (File.Exists(Temp2Db))
                File.Delete(Temp2Db);
        }
        System.Runtime.InteropServices.Marshal.ReleaseComObject(DBE);
        DBE = null;
    }
    catch (Exception ex)
    {
        // Do something with the error
    }
    return retVal;
}

Do I need to install ACE on every machine I want to use the program on? — Scorch, Nov 24 '17 at 12:59
Yes, if it doesn't already exist. If Microsoft Office is installed, then ACE is likely already on the machine. ACE doesn't come bundled with Windows yet. JET DAO has been bundled with Windows since Windows XP. — Jericho Johnson, Nov 25 '17 at 14:06

C# Compact and repair .accdb files using DAO or ADOX

1 Answers1