Microsoft.Jet.OLEDB.4.0 ProgID is not available (Windows 7 but probably not only)

It was a sort of ridiculous problem: an attempt to instantiate a Microsoft.Jet.OLEDB.4.0 object failed with error. Still some applications are still running without problems connecting to Jet databases, how comes?

There has been a number of posts on Internet, but none of the top ones appeared to be relevant.

The problem is reproduced extremely simple:

int _tmain(int argc, _TCHAR* argv[])
{
    ATLVERIFY(SUCCEEDED(CoInitialize(NULL)));
    {
        //CoLoadLibrary(L"C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\oledb32.dll", TRUE);
        CComPtr<IDBInitialize> pDbInitialize;
        const HRESULT nResult = pDbInitialize.CoCreateInstance(L"Microsoft.Jet.OLEDB.4.0", NULL, CLSCTX_INPROC_SERVER);
        _tprintf(_T("nResult 0x%08x\n"), nResult);
    }
    CoUninitialize();
    return 0;
}

Oops, the code gives error REGDB_E_CLASSNOTREG 0x80040154 “Class not registered”. It looked like system was unable to locate one of the internally used DLLs – oledb32.dll, and if we help by uncommenting the line commented in the code snippet above, the error changes to ERROR_MOD_NOT_FOUND 0x8007007e “The specified module could not be found”.

The problem appears to be that one of the system components, which is involved, “Microsoft OLE DB Data Conversion Library” is registered with the system using a REG_EXPAND_SZ value, to be located using path “%CommonProgramFiles(x86)%\System\Ole DB\oledb32.dll”. It is obvious that CommonProgramFiles(x86) is placeholder to be expanded, but does the expansion succeed?

On my system there was no such environment string, so expansion failed. There however was CommonProgramFiles, and it pointed to correct location. So, this hot fix makes it work right:

BOOL UpdateEnvironmentVariables()
{
    CString sCommonProgramFilesX86;
    sCommonProgramFilesX86.GetEnvironmentVariable(_T("CommonProgramFiles(x86)"));
    if(sCommonProgramFilesX86.IsEmpty())
    {
        CString sCommonProgramFiles;
        sCommonProgramFiles.GetEnvironmentVariable(_T("CommonProgramFiles"));
        if(!sCommonProgramFiles.IsEmpty())
        {
            ATLVERIFY(SetEnvironmentVariable(_T("CommonProgramFiles(x86)"), sCommonProgramFiles));
            return TRUE;
        }
    }
    return FALSE;
}

int _tmain(int argc, _TCHAR* argv[])
{
    ATLVERIFY(SUCCEEDED(CoInitialize(NULL)));
    {
        UpdateEnvironmentVariables();
        CComPtr<IDBInitialize> pDbInitialize;
        const HRESULT nResult = pDbInitialize.CoCreateInstance(L"Microsoft.Jet.OLEDB.4.0", NULL, CLSCTX_INPROC_SERVER);
        _tprintf(_T("nResult 0x%08x\n"), nResult);
    }
    CoUninitialize();
    return 0;
} 

I am under impression that the problem is not specific to Windows 7. I remember similar symptoms on Windows XP system, but they were not critical and not investigated to solution. It rather seems that OLE DB component is registering itself using a reference to environment variable which is not present on a number of systems.