CODEWITHSUNDEEP
c#ms-accessdaoadox
Scorch
Scorch

Reputation: 173

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

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?

Upvotes: 1

Views: 2222

Answers (1)

Jericho Johnson
Jericho Johnson

Reputation: 759

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;
}

Upvotes: 3

Related Questions