Directly access older MDB files

Question

I have a C++ (MFC) application. My customer needs me to add a feature to read from some data files.

These data files are MDB files. They appear to be for older versions of Microsoft Access (probably versions prior to the year 2007, but I haven't been able to confirm that).

I'm up for reading these files directly if I can find sufficient documentation on their format. I'm also up to hearing about older ODBC or other similar tools that would work with these older files. But a requirement is that we don't need to install a bunch of additional software. Ideally, I can do this all from C++.

Could I get some suggestions on how I might access this data? Where is the format documents and what might be some other options than reading it directly myself?

Note: This article presents the layout of an MDB file; however, it does not seem to match the content of the files I'm working with.

Answer 1

I use CDatabase with the Microsoft Jet driver to connect to MDB databases.

I locate the correct driver like this:

// We now iterate the JET drivers list and locate a valid MDB driver
CString CPTSDatabase::GetJETDriver(bool bAccDbMode)
{
    CString         strDriver;
    CString         strName, strNameLower, strValue;
    CString         strDefaultDriver = _T("Microsoft Access Driver (*.mdb)");
    CString         strDBType = _T("(*.mdb)");
    CStringArray    aryStrDrivers;
    TCHAR           szBuf[2001];
    WORD            cbBufMax = 2000;
    WORD            cbBufOut;
    TCHAR           *pszBuf = szBuf;

    if (SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
    {
#ifdef _WIN64
        strDefaultDriver = _T("Microsoft Access Driver (*.mdb, *.accdb)");
        strDBType = _T("(*.mdb, *.accdb)");
#else
        if (bAccDbMode)
        {
            strDefaultDriver = _T("Microsoft Access Driver (*.mdb, *.accdb)");
            strDBType = _T("(*.mdb, *.accdb)");
        }
#endif
        do 
        {
            strName = CString(pszBuf);
            strNameLower = strName;
            strNameLower.MakeLower();

            if (strNameLower.Find(strDBType) != -1)
            {
                aryStrDrivers.Add(strName);
                if (strName.CollateNoCase(strDefaultDriver) == 0)
                {
                    strDriver = strName;
                    break;
                }
            }

            pszBuf = _tcschr(pszBuf, _T('\0')) + 1;
        } while (pszBuf[1] != _T('\0'));

        if (strDriver.IsEmpty() && aryStrDrivers.GetSize() > 0)
        {
            // Try and use the first MDB driver we found
            strDriver = aryStrDrivers.GetAt(0);
        }
    }

    // Make a note of the driver
    AfxGetApp()->WriteProfileString(_T("Options"), _T("JET Connection Driver"), strDriver);

    return strDriver;
}

If you know which driver you want to use, the just use that. Then open your database:

// Opens the database (gets closed in destructor)
void CPTSDatabase::OpenDatabase(bool bAccDbMode, CString strPassword)
{
    CString     strDBConnectString;
    CString     strDriver;

    if (m_dbDatabase.IsOpen())
        return;

    if (DatabaseExist(m_strDatabasePath))
    {
        // AJT v10.5.0 Take into account the DB mode
        strDriver = GetJETDriver(bAccDbMode);
        // Take into account the DB password (decrypted!)
        strDBConnectString.Format(_T("Driver={%s};DBQ=%s;Pwd=%s"),
            strDriver, m_strDatabasePath,
            CPTSTools::DecryptDatabasePassword(strPassword));

        m_dbDatabase.OpenEx(strDBConnectString, CDatabase::noOdbcDialog);
    }
}

But a word of warning .... And I use my own computer as an example ....

My computer is Windows 10 64 bit ...

It has:

• a 64 bit ACCDB Microsoft Database driver
• a 32 bit MDB Microsoft Database driver

There is no 64 bit MDB driver. So if you want to work with MDB databases make sure you build your application in 32 bit mode.

Answer 2

All you need to read MS Access .mdb files is MDAC (Microsoft Data Access Components) installed on target PC/server. All modern OSs like Vista/Windows 7/8/8.1/10 have this component pre-installed for your convinience. If you have to target XP you can download this component from MS site. Also the InstallShield comes with MDAC 2.7 merge module if you need to build installer for your app.

You can simply use the standard set of MFC classes to work with databases:

CDatabase database;
CString sDsn;
CString sFile = _T("D:\\Projects\\DB\\Test.mdb");

// Build ODBC connection string
sDsn.Format(_T("ODBC;DRIVER={%s};DSN='';DBQ=%s"), _T("MICROSOFT ACCESS DRIVER (*.mdb)"), sFile);
try
{
    // Open the database
    database.Open(NULL, FALSE, FALSE, sDsn);

    // Allocate the recordset
    CRecordset recset( &database );

    // Build the SQL statement
    CString sSqlString =  _T("SELECT Field1, Field2 from MyTable");

    // Execute the query
    recset.Open(CRecordset::forwardOnly, sSqlString, CRecordset::readOnly);

    // Loop through each record
    while( !recset.IsEOF() )
    {
        // Copy each column into a variable
        CString sField1;
        CString sField2;
        recset.GetFieldValue(_T("Field1"), sField1);
        recset.GetFieldValue(_T("Field2"), sField2);

        // goto next record
        recset.MoveNext();
    }
    // Close the database
    database.Close();
}
catch(CDBException* e)
{
    // If a database exception occured, show error msg
    AfxMessageBox(_T("Database error: ") + e->m_strError);
}