Compacting Microsoft Access Database without using DAO

Question

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

Does this answer your question? [How can we compact a access database in VB.NET or C#.NET without using DAO and JRO](https://stackoverflow.com/questions/685012/how-can-we-compact-a-access-database-in-vb-net-or-c-net-without-using-dao-and-j) — Neil, May 25 '22 at 12:02
@Neil I just downloaded Jetcomp to try it but it only works with MDB files and not ACCDB files. — Andrew Truckle, May 25 '22 at 12:37
If you have the ODBC driver, you have DAO as well. They're both part of the Access Database Engine (which also comes with Access Runtime/the full application). You can use late-bound COM if you don't want to depend on a specific version. — Erik A, May 26 '22 at 07:00
See https://stackoverflow.com/q/403218/7296893 for an example — Erik A, May 26 '22 at 09:05
@ErikA Thanks for the linked answer. I have got as far as `dynamic ac = Activator.CreateInstance(Type.GetTypeFromProgID("Access.Application"));` but can't work out where to go from there to perform the compacting. — Andrew Truckle, May 26 '22 at 09:48
Tip: You want `DAO.DbEngine`, not `Access.Application`. You should be able to just invoke the `CompactDatabase` method. Kinda busy now, if you can't figure it out I'll probably have time to write a full example later. — Erik A, May 26 '22 at 10:01
@ErikA Thanks. I’ll keep trying. Struggling because there is no intelisense but I understand why. It has to support a db password if it exists. I will try some more. — Andrew Truckle, May 26 '22 at 10:33
@ErikA It doesn't work for me. In the registry I can see `DAO.DBEngine.120`. — Andrew Truckle, May 26 '22 at 12:10
@ErikA I have encountered an exception when shutting the app down. I started a new question. — Andrew Truckle, May 26 '22 at 13:41
That looks like it's caused by passing `null` to the third and fourth parameter. Try passing `";LANGID=0x0409;CP=1252;COUNTRY=0"` as the third and 0 as the fourth. Optional parameters with COM and C# is a pain, `null` is different from not passing the parameter — Erik A, May 26 '22 at 14:21
@ErikA Thanks, although my actual app has several versions of the database in different languages. So I would have to cater for that. But ... for english using your options, it made no different. — Andrew Truckle, May 26 '22 at 14:36
@ErikA Interestingly, if I #include afxdao and make a call to `AfxDaoTerm();` after I have run my dll function and compile it says that "DAO Database classes are not supported for Win64 platforms." — Andrew Truckle, May 26 '22 at 14:42

Andrew Truckle · Accepted Answer · 2022-05-26T13:09:39.350

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

Compacting Microsoft Access Database without using DAO

1 Answers1

Linked