I use the CDatabase class to open a ACCDB Access database. The driver is: _T("Microsoft Access Driver (*.mdb, *.accdb)").

I can open and use the database OK (have done so for many years):

if (DatabaseExist(m_strMDBPath))
{
    // AJT v10.5.0 Take into account the DB mode
    strDriver = GetJETDriverEx(bAccDbMode);

    // AJT v10.5.0 Take into account the DB password (decrypted!)
    strDBConnectString.Format(_T("Driver={%s};DBQ=%s;Pwd=%s"),
        (LPCTSTR)strDriver, (LPCTSTR)m_strMDBPath,
        (LPCTSTR)m_toolsMSA.DecryptDatabasePassword(strPassword));

    try
    {
        //OutputDebugString(L"DB Open - Begin\n");
        if (m_Database.OpenEx(strDBConnectString, CDatabase::noOdbcDialog))
        {
            //OutputDebugString(L"DB Open - End\n");
        }
    }
    catch (CDBException* e)
    {
        // If a database exception occurred, show error msg
        AfxMessageBox(L"Database error: "   e->m_strError);
    }
}

I have an issue with compacting the database programmatically. Previously I was using DAO:

Microsoft.Office.Interop.Access.Dao

But that required the Access 2013 Runtime to be installed. And I don't want to install it anymore because I have Access 2016 installed on my PC.

The code that did the compacting used the DBEngine command:

Microsoft.Office.Interop.Access.Dao.DBEngine objDbEngine = new Microsoft.Office.Interop.Access.Dao.DBEngine();

But now I need a new way to compact the database as I no longer have the DAO reference. The end users might not have Access 2016 installed but I would tell them to install the Access 2016 runtime.

What alternative can I use to programatically compact this ACCDB in my MFC project?

Note that I have an underlying C# .Net DLL tool so I can add any such Compacting to that C# library if it is easier than C .

This is how I did it before with DAO which needed 2013 runtime:

I have tried this:

#pragma once
#import "C:\\Program Files\\Microsoft Office\\root\\Office16\\ACEDAO.DLL" named_guids rename("EOF", "ACEDAO_EOF")  


static void Test(CString strDB, CString strDbCompacted)
{
    CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);
    DAO::_DBEngine* pDBEngine = NULL;
    HRESULT hr = CoCreateInstance(__uuidof(DAO::DBEngine), NULL, CLSCTX_INPROC_SERVER, IID_IDispatch, (LPVOID*)&pDBEngine);
    if (SUCCEEDED(hr) && pDBEngine)
    {
        _bstr_t bstrDatabase = strDB.AllocSysString();
        _bstr_t bstrDatabaseCompacted = strDbCompacted.AllocSysString();
        try
        {
            pDBEngine->CompactDatabase(bstrDatabase, bstrDatabaseCompacted, L";pwd=xxx");
        }
        catch (_com_error& e)
        {
            _bstr_t bstrSource(e.Source());
            _bstr_t bstrDescription(e.Description());
            WCHAR wsBuffer[255];
            wsprintf(wsBuffer, L"Source : %s\nDescription : %s", (LPCTSTR)bstrSource, (LPCTSTR)bstrDescription);
            MessageBox(NULL, wsBuffer, L"Error", MB_OK | MB_ICONSTOP);
        }
        pDBEngine->Release();
    }
}

But it fails and does not even get to the catch handler.

I am just looking for a simple way to support compact, change password for the MDB and ACCDB ... I even tried adding the acedao.dll as a reference to a C# console project and it would not accept it.

I tried to use late binding, as referred to in the comments. This is the function:

public void CompactAccessDatabase(string strSourceDB, string strTargetDB, string strPassword)
{
    try
    {
        dynamic dbEngine = Activator.CreateInstance(Type.GetTypeFromProgID("DAO.DbEngine"));

        string strDecryptedPassword = "";
        if(!string.IsNullOrEmpty(strPassword))
        {
            if (!DecryptDatabasePassword(strPassword, ref strDecryptedPassword))
            {
                SimpleLog.Log("DecryptDatabasePassword returned false");
            }
            strDecryptedPassword = "pwd="   strDecryptedPassword;
        }
        dbEngine.CompactDatabase(strSourceDB, strTargetDB, null, null, strDecryptedPassword);
    }
    catch (Exception ex)
    {
        SimpleLog.Log(ex);
    }
}

But I am getting an exception:

<LogEntry Date="2022-05-26 13:06:49" Severity="Exception" Source="MSAToolsLibrary.MSAToolsLibraryClass.CompactAccessDatabase" ThreadId="1">
  <Exception Type="System.ArgumentNullException" Source="System.Activator.CreateInstance">
    <Message>Value cannot be null.
Parameter name: type</Message>
    <StackTrace>   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.Activator.CreateInstance(Type type)
   at MSAToolsLibrary.MSAToolsLibraryClass.CompactAccessDatabase(String strSourceDB, String strTargetDB, String strPassword) in D:\My Programs\2022\MSAToolsLibrary\MSAToolsLibrary\MSAToolsLibraryClass.cs:line 1649</StackTrace>
  </Exception>
</LogEntry>

https://stackoverflow.com/a/29252649/2287576

CodePudding user response：

Based on the comments and the principle referred to in a linked answer I was eventually able to come up with this code with works for my 64 bit build:

public void CompactAccessDatabase(string strSourceDB, string strTargetDB, string strPassword)
{
    try
    {
        dynamic dbEngine = Activator.CreateInstance(Type.GetTypeFromProgID("DAO.DBEngine.120"));

        string strDecryptedPassword = "";
        string strDBConnectionString = "";
        if(!string.IsNullOrEmpty(strPassword))
        {
            if (!DecryptDatabasePassword(strPassword, ref strDecryptedPassword))
            {
                SimpleLog.Log("DecryptDatabasePassword returned false");
            }
            strDBConnectionString = ";pwd="   strDecryptedPassword;
        }
        dbEngine.CompactDatabase(strSourceDB, strTargetDB, null, null, strDBConnectionString);
    }
    catch (Exception ex)
    {
        SimpleLog.Log(ex);
    }
}