Developing Access 2007 Solutions with Native C or C++

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn about the architecture of Microsoft Office Access 2007, its new ACE engine and features, and find out what things that you should consider when you choose the most optimal native data access API for your new or legacy database solution. Get started quickly developing C or Microsoft Visual C++ 2008 solutions for Access databases. (11 printed pages)

Aleksandar Jakšić, Microsoft Corporation

August 2008

Applies to: Microsoft Office Access 2007

Related Technologies: Microsoft Office Access 2007 Runtime, Microsoft Data Access Components (MDAC) 2.8, MDAC 2.8 Service Pack (SP) 1 for Windows XP SP2, MDAC 2.8 SP2 for Windows Server 2003 SP1, Microsoft Windows Data Access Components (Windows DAC) 6.0, Microsoft Visual C++ 2008

Contents

• Overview

• What About the Microsoft JET Engine?

• Code Examples

• Prerequisites

• Access 2007 File Formats

• Overview of Data Access Technologies

• DAO

• OLE DB

• ADO

• ODBC

• Performance Results

• Considerations when Choosing Data Access Technology

• Deprecated Data Access Methods

• Conclusion

Overview

If you are a database developer and you want to know more about the options that are available to you when you develop a native solution for Microsoft Office Access 2007 using C or C++, this article is written for you. The subject of data access technologies is large, and because many APIs, DLLs, and frameworks interact with the new engine in Access 2007, which is known as the Microsoft Access Database Engine (ACE Engine), this guide is directed toward a broad developer audience. This article does not give you detailed descriptions of every data access technology mentioned in this article. Instead, it describes the data access options available to you when you work with Access 2007 solutions and the advantage and disadvantage of each approach. The accompanying code examples are intended to help you get started quickly, even if you have never programmed in C or C++ before.

This article answers questions such as the following:

• What’s the fastest way to programmatically work with the Access databases generally?

• Is there a way to connect to the ACE engine using the C programming language (ANSI/ISO C)?

• Can I use my legacy Microsoft Foundation Classes (MFC) Data Access Object (DAO) code to work with Access 2007 databases?

What About the Microsoft JET Engine?

Access 2007 introduces an architectural change that affects all data access technologies that are available to Access developers.

Prior to Access 2007, Access used the Microsoft Joint Engine Technology (JET) engine for its data storage and query processing. Even though JET was generally accepted as part of Access, since Microsoft Windows 2000 it was included as part of the Windows operating system. However, the JET engine is no longer distributed with the latest Microsoft Data Access Components (MDAC). As a result, Access 2007 provides a new engine based on JET, which is known as the Microsoft Access Engine (ACE Engine), instead of using the deprecated Microsoft JET engine.

The ACE engine is fully backward-compatible with previous versions of the JET engineso that it reads and writes (.mdb) files from earlier Access versions. Access 2007 also introduces a new default file format (.accdb) that supports many product improvements. Because the Access team now owns its engine, developers can be confident that their Access solutions will continue to work in the future.

Because the ACE engine is not part of the Windows operating system, application users must have Access 2007 or download the free Access 2007 Download: Access Runtime. You can also use the 2007 Office System Driver: Data Connectivity Components installed on your computer.

Code Examples

This article presents five complete programs created by using Microsoft Visual C++ 2008 that are available for download. Source code is compatible with older versions of Visual C++. However, you might have to create additional project files for the Visual C++ version with which you want to compile this code.

All programs perform semantically-identical steps by demonstrating syntactical differences in the data access technologies that are used. Moreover, the console output of each program is almost identical. Examples present the basics of data access that include how to connect to a database, and how to work with a recordset on an SQL query.

Download sample files: Access 2007 Sample: Developing Access 2007 Solutions with Native C or C++

Here is the list of steps that are performed in each code example:

1. Define the connection string.

2. Connect to the C:\Northwind 2007.accdb database.

3. Verify the connection and display data source name.

4. Build the SQL query.

5. Execute the query and create a recordset.

6. Retrieve and display a schema of the recordset.

7. Retrieve and display the actual data.

8. Display the total number of retrieved rows.

9. Close all handles.

10. Handle the errors and exceptions when it is required.

I used similar code to measure an overall speed against these libraries. Additionally, the performance results are published in this article.

Security Note

The code examples show a concept and should not be used in applications or Web sites, as they do not show safe coding practices. For example, all of the code examples hard code the default user name (Admin) and a blank password. This is not recommended. Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

Prerequisites

It is assumed that you are comfortable with native C or C++ programming, the Visual Studio environment, and are familiar with the concept of relational databases and SQL.

Access 2007 File Formats

This section summarizes the new and legacy file name extensions that are used by Access 2007.

Access 2007 introduces a new default file format, .accdb, that supports several improvements in the product that include complex data types such as multivalue fields, the Attachment data type and history tracking in Memo fields. It also offers better integration with Microsoft Office SharePoint Server 2007 and Microsoft Office Outlook 2007, as well as security and encryption improvements. Although Access 2007 continues to provide support for many of the file formats that were used in previous versions (including .mdb databases) for backward-compatibility, the new .accdb file format is set as the future standard. It is recommended that you use the new file formats to take advantage of the many new features and benefits that they provide.

Among the other new file formats is .accde, which is the file name extension for Access 2007 files that are compiled into an "execute only" file. This format replaces the .mde file name extension that is used in previous versions of Access. The .accdr file is a file name extension that enables you to open a database in run-time mode and .accdt is the file name extension for Access Database Templates.

For more information about the Access file formats, see Developer Considerations for Choosing File Formats in Access 2007.

Overview of Data Access Technologies

Visual C++ 2008 provides several ways to work with Access databases. The following technologies are traditionally available to the Access developers who are writing their applications in a C or C++ environment:

• Data Access Object (DAO)

• OLE DB

• ActiveX Data Objects (ADO)

• Open Database Connectivity (ODBC)

Although Access engine providers (components such as the ACE version of DAO, the ACE OLE DB provider and the ACE ODBC provider) were originally included with Windows as part of Microsoft Data Access Components (MDAC), they are now distributed with Access (except ADO which is still part of the Microsoft Windows DAC). ODBC, OLE DB, and ADO themselves are all system-level components.

The following figure presents the architecture of the ACE engine in the native programming environment. From the data access perspective, you can view Access as the way the ACE engine is exposed graphically to users.

Figure 1. Architecture of the ACE engine in the native programming environment.

The following table summarizes the native data access methods.

Table 1. Native Data Access Methods

Provider	Data Access Method	Header File & Connection Info	Supported Language	Additional Comments
ACE DAO	Direct DAO	acedao.tlh (generated from acedao.dll); acedao.dll	C++	Supports new .accdb format files. This method is recommended for native projects because it exposes many of the new features that are introduced in the new ACE engine. This method is currently available only for the 32-bit version of the Windows operating system.
Microsoft Access Engine OLE DB Provider (ACE Provider)	ATL OLE DB	<atldbcli.h> and <atldbsch.h>; aceoledb.dll Microsoft.ACE.OLEDB.12.0	C++	Supports new .accdb format files together with limited support for new features such as complex data and security. This method is currently available only for the 32-bit version of the Windows operating system.
ADO	ADO	msado15.tlh (generated from msado15.dll); Installed with MDAC 2.8 or Windows DAC 6.0.	C++	Supports new .accdb format files together with limited support for new features such as complex data and security. This method is available for the 64-bit version of the Windows operating system as part of DAC 6.0. It also serves as an alternative to OLE DB provider.
Access ODBC Driver	Direct ODBC	<sqlext.h>; aceodbc.dll; Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file	C,C++	Supports new .accdb format files. This method is currently available only for the 32-bit version of the Windows operating system. It is recommended only for maintenance of existing applications or when that you need to work with ANSI/ISO C.
Access ODBC Driver	MFC ODBC	<afxdb.h>; aceodbc.dll; Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file	C++	Supports new .accdb format files. This data access method is recommended only for maintenance of the existing applications.

Forf a list of data access methods that are deprecated with Access 2007 release, see Deprecated Data Access Methods.

DAO

Previously, Data Access Objects (DAO) was the exclusive data access method for Access developers. There are two ways to connect to an Access 2007 database using the DAO API:

• MFC DAO. The MFC DAO classes give the user a way to use the legacy Microsoft JET Database Engine. However, they are not supported in Access 2007. DAO 3.6 is the final version of this technology. It is not available on the 64-bit Windows operating system. Visual C++ .NET wizards do not generate code that automatically creates and opens recordsets.

• Direct DAO. This access method works with Access 2007 databases. It provides the best support for new features that are introduced with Access 2007 because it exposes most of the ACE engine functionality.

Direct DAO Example

Direct DAO uses acedao.dll. To compile this code, use the #import macro to generate .tlh header by specifying directive similar to the one shown in the following example.

#import <C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE12\\ACEDAO.dll> rename( "EOF", "AdoNSEOF" )

Because we connect directly to the engine, use the database location for the connection information.

_bstr_t bstrConnect = "C:\\Northwind 2007.accdb";

The following example fetches the schema and data.

// Create an instance of the engine.
DAO::_DBEngine* pEngine = NULL;

// The CoCreateInstance helper function provides a convenient shortcut by connecting 
// to the class object associated with the specified CLSID, creating an 
// uninitialized instance, and releasing the class object. 
hr = CoCreateInstance(
    __uuidof(DAO::DBEngine),
    NULL,
    CLSCTX_ALL,
    IID_IDispatch,
    (LPVOID*)&pEngine);
if (SUCCEEDED(hr) && pEngine)
{
    // COM errors are handled by the C++ try/catch block.
    try
    {
        DAO::DatabasePtr pDbPtr = NULL;
        pDbPtr = pEngine->OpenDatabase(bstrConnect);
        if (pDbPtr)
        {
            cout<<DAM<<": Successfully connected to database. Data source name:\n  "
                <<pDbPtr->GetName()<<endl;

            // Prepare SQL query.
            _bstr_t query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";
            cout<<DAM<<": SQL query:\n  "<<query<<endl;

            // Excecute the query and create a record set.
            DAO::RecordsetPtr pRS = NULL;
            pRS = pDbPtr->OpenRecordset(query, _variant_t(DAO::dbOpenDynaset));
            if (pRS && 0 < pRS->RecordCount)
            {
                cout<<DAM<<": Retrieve schema info for the given result set: "<<endl;
                DAO::FieldsPtr pFields = NULL;
                pFields = pRS->GetFields();
                if (pFields && pFields->Count > 0)
                {
                    for (short nIndex=0; nIndex < pFields->Count; nIndex++)
                    {
                        cout<<" | "<<pFields->GetItem(nIndex)->GetName();
                    }
                    cout<<endl;
                }
                else
                {
                    cout<<DAM<<": Error: Number of fields in the result set is 0."<<endl;
                }

                cout<<DAM<<": Fetch the actual data: "<<endl;
                // Loop through the rows in the result set.
                while (!pRS->AdoNSEOF)
                {
                    for (short nIndex=0; nIndex < pFields->Count; nIndex++)
                    {
                        cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetValue());
                    }
                    cout<<endl;
                    pRS->MoveNext();
                }
                cout<<DAM<<": Total Row Count: "<<pRS->RecordCount<<endl;
            }

            // Close record set and database.
            pRS->Close();
            pDbPtr->Close();
            pDbPtr = NULL;
        }
        else
        {
            cout<<DAM<<": Unable to connect to data source: "<<bstrConnect<<endl;
        }
    }
    catch(_com_error& e)
    {
        cout<<DAM<<": _com_error: "<<e.Description()<<endl;
    }
    
    pEngine->Release();
    pEngine = NULL;
    cout<<DAM<<": Cleanup. Done."<<endl;
}
else
{
    cout<<DAM<<": Cannot instantiate DBEngine object."<<endl;
}

The following example shows the program output.

Direct DAO: Successfully connected to database. Data source name:
  C:\Northwind 2007.accdb
Direct DAO: SQL query:
  SELECT Customers.[Company], Customers.[First Name] FROM Customers;
Direct DAO: Retrieve schema info for the given result set:
 | Company | First Name
Direct DAO: Fetch the actual data:
 | Company A | Anna
 | Company B | Antonio
 | Company C | Thomas
 | Company D | Christina
 | Company E | Martin
 | Company F | Francisco
 | Company G | Ming-Yang
 | Company H | Elizabeth
 | Company I | Sven
 | Company J | Roland
 | Company K | Peter
 | Company L | John
 | Company M | Andre
 | Company N | Carlos
 | Company O | Helena
 | Company P | Daniel
 | Company Q | Jean Philippe
 | Company R | Catherine
 | Company S | Alexander
 | Company T | George
 | Company U | Bernard
 | Company V | Luciana
 | Company W | Michael
 | Company X | Jonas
 | Company Y | John
 | Company Z | Run
 | Company AA | Karen
 | Company BB | Amritansh
 | Company CC | Soo Jung
Direct DAO: Total Row Count: 29

OLE DB

OLE DB is the Microsoft system-level programming interface for accessing data. It is a specification instead of a set of components or files. It is the underlying technology for ADO and a source of data for Microsoft ADO.NET. OLE DB specifies a set of Microsoft COM interfaces that encapsulate various database management system services to serve consumers. OLE DB is an open standard for accessing all kinds of data including Access databases. It supports various development needs that include creating front-end database clients and middle-tier business objects by using live connections to data in relational databases and other stores.

ATL OLE DB Example

This example uses Active Template Library (ATL) by including <atldbcli.h> and <atldbsch.h>. The connection information uses the Microsoft.ACE.OLEDB.12.0 data provider implemented in aceoledb.dll.

LPCOLESTR lpcOleConnect = L"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Northwind 2007.accdb;User Id=admin;Password=;";

The following example fetches the schema and data.

// To initialize the connection to a database using an OLE DB provider, 
// two ATL classes are needed: CDataSource and CSession.
CDataSource dbDataSource;
CSession dbSession;

// Uses ATL's string conversion macros to convert between character encodings.
USES_CONVERSION;

// Open the connection and initialize the data source specified by the passed 
// initialization string.
hr = dbDataSource.OpenFromInitializationString(lpcOleConnect);
if (FAILED(hr))
{
    cout<<DAM<<": Unable to connect to data source "<<OLE2T(lpcOleConnect)<<endl;
}
else
{
    hr = dbSession.Open(dbDataSource);
    if (FAILED(hr))
    {
        cout<<DAM<<": Couldn't create session on data source "<<OLE2T(lpcOleConnect)<<endl;
    }
    else
    {
        CComVariant var;
        hr = dbDataSource.GetProperty(DBPROPSET_DATASOURCEINFO, DBPROP_DATASOURCENAME, &var);
        if (FAILED(hr) || (var.vt == VT_EMPTY))
        {
            cout<<DAM<<": No Data Source Name Specified."<<endl;
        }
        else
        {
            cout<<DAM<<": Successfully connected to database. Data source name:\n  "
                <<COLE2T(var.bstrVal)<<endl;
            
            // Prepare SQL query.
            LPCOLESTR query = L"SELECT Customers.[Company], Customers.[First Name] FROM Customers;";
            cout<<DAM<<": SQL query:\n  "<<OLE2T(query)<<endl;

            // Excecute the query and create a record set.
            CCommand<CDynamicStringAccessor> cmd;
            hr = cmd.Open(dbSession, query);
            DBORDINAL colCount = cmd.GetColumnCount();
            if (SUCCEEDED(hr) && 0 < colCount)
            {
                cout<<DAM<<": Retrieve schema info for the given result set: "<<endl;
                DBORDINAL cColumns;
                DBCOLUMNINFO* rgInfo = NULL;
                OLECHAR* pStringsBuffer = NULL;
                cmd.GetColumnInfo(&cColumns, &rgInfo, &pStringsBuffer);
                for (int col=0; col < (int)colCount; col++)
                {
                    cout<<" | "<<OLE2T(rgInfo[col].pwszName);
                }
                cout<<endl;

                cout<<DAM<<": Fetch the actual data: "<<endl;
                int rowCount = 0;
                CRowset<CDynamicStringAccessor>* pRS = (CRowset<CDynamicStringAccessor>*)&cmd;
                // Loop through the rows in the result set.
                while (pRS->MoveNext() == S_OK)
                {
                    for (int col=1; col <= (int)colCount; col++)
                    {
                        CHAR* szValue = cmd.GetString(col);
                        cout<<" | "<<szValue;
                    }
                    cout<<endl;
                    rowCount++;
                }
                cout<<DAM<<": Total Row Count: "<<rowCount<<endl;
            }                   
            else
            {
                cout<<DAM<<": Error: Number of fields in the result set is 0."<<endl;
            }
        }  
    }
}

dbDataSource.Close();
dbSession.Close();
cout<<DAM<<": Cleanup. Done."<<endl;

ADO

ActiveX Data Objects (ADO) provides a COM-based application-level interface for OLE DB data providers. Although it decreases performance compared to coding to OLE DB directly, ADO is straightforward to learn and use. ADO gives C++ programmers access to the underlying OLE DB interfaces. Most developers are typically not interested in such a low level of control such as managing memory resources and manually aggregating components that OLE DB gives over the data access process.

Instead of relying on a single database engine such is DAO, ADO is designed to use a common programming model to access data universally.

ADO Example

ADO uses the msado15.dll library, which includes MDAC 2.8. To compile this code, you must use #import macro to generate .tlh header by specifying directive similar to the one shown in the following example.

#import <C:\\Program Files\\Common Files\\System\\ado\\msado15.dll> rename( "EOF", "AdoNSEOF" )

For connection information, use the Microsoft.ACE.OLEDB.12.0 data provider implemented in aceoledb.dll.

_bstr_t bstrConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Northwind 2007.accdb;";

The following example fetches the schema and data.

// Shows the Data Access Method used in this sample.
const char* DAM = "ADO";

ADODB::_ConnectionPtr pConn("ADODB.Connection");
hr = pConn->Open(bstrConnect, "admin", "", ADODB::adConnectUnspecified);
if (SUCCEEDED(hr))
{
    cout<<DAM<<": Successfully connected to database. Data source name:\n  "
       <<pConn->GetConnectionString()<<endl;

    // Prepare SQL query.
    _bstr_t query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";
    cout<<DAM<<": SQL query:\n  "<<query<<endl;

    // Excecute the query and create a record set.
    ADODB::_RecordsetPtr pRS("ADODB.Recordset");
    hr = pRS->Open(query, 
            _variant_t((IDispatch *) pConn, true), 
            ADODB::adOpenUnspecified,
            ADODB::adLockUnspecified, 
            ADODB::adCmdText);
    if (SUCCEEDED(hr))
    {
        cout<<DAM<<": Retrieve schema info for the given result set: "<<endl;
        ADODB::Fields* pFields = NULL;
        hr = pRS->get_Fields(&pFields);
        if (SUCCEEDED(hr) && pFields && pFields->GetCount() > 0)
        {
            for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
            {
                cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetName());
            }
            cout<<endl;
        }
        else
        {
            cout<<DAM<<": Error: Number of fields in the result set is 0."<<endl;
        }

        cout<<DAM<<": Fetch the actual data: "<<endl;
        int rowCount = 0;
        while (!pRS->AdoNSEOF)
        {
            for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++)
            {
                cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetValue());
            }
            cout<<endl;
            pRS->MoveNext();
            rowCount++;
        }
        cout<<DAM<<": Total Row Count: "<<rowCount<<endl;
    }
   
    pRS->Close();
    pConn->Close();
    cout<<DAM<<": Cleanup. Done."<<endl;
}
else
{
    cout<<DAM<<": Unable to connect to data source: "<<bstrConnect<<endl;
}

ODBC

ODBC (Open Database Connectivity) is the oldest of the current data access technology at Microsoft designed to enable you to create a common code base that provides access to various relational data stores. Its methods are exposed in a traditional, non-object–oriented, C language API.

There are two ways to connect to an Access database through the ODBC API:

• Direct ODBC. This data access method is recommended only for maintenance of the existing applications or when you must work with ANSI/ISO C.

• MFC ODBC. This data access method is recommended only for maintenance of the existing applications.

Direct ODBC Example

The following example shows the connection information for Direct ODBC, which uses the {Microsoft Access Driver (*.mdb, *.accdb)} driver implemented in aceodbc.dll library by including <sqlext.h>.

char szDSN[256] = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\Northwind 2007.accdb;";

The following example fetches the schema and data.

Note
This code is written in C not Visual C++.

Note

This code is written in C not Visual C++.

/* Data Access Method used in this sample */
const char* DAM = "Direct ODBC";

HENV    hEnv;
HDBC    hDbc;

/* ODBC API return status */
RETCODE rc;

int     iConnStrLength2Ptr;
char    szConnStrOut[256];

unsigned char* query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";

SQLCHAR         chval1[128], chval2[128], colName[128];
int             ret1;
int             ret2;

/* Number of rows and columns in result set */
SQLINTEGER      rowCount = 0;
SQLSMALLINT     fieldCount = 0, currentField = 0;
HSTMT           hStmt;

/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);
/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);

/* Connect to the 'Northwind 2007.accdb' database */
rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, 
    SQL_NTS, (unsigned char*)szConnStrOut, 
    255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc)) 
{
    printf("%s: Successfully connected to database. Data source name: \n  %s\n", 
       DAM, szConnStrOut);

    /* Prepare SQL query */
    printf("%s: SQL query:\n  %s\n", DAM, query);

    rc = SQLAllocStmt(hDbc,&hStmt);
    rc = SQLPrepare(hStmt, query, SQL_NTS);
   
    /* Bind result set columns to the local buffers */ 
    rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, chval1, 128, (SQLINTEGER*)&ret1);
    rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, (SQLINTEGER*)&ret2);
   
    /* Excecute the query and create a record set */
    rc = SQLExecute(hStmt); 
    if (SQL_SUCCEEDED(rc)) 
    {
        printf("%s: Retrieve schema info for the given result set:\n", DAM);
        SQLNumResultCols(hStmt, &fieldCount);
        if (fieldCount > 0)
        {
            for (currentField=1; currentField <= fieldCount; currentField++)
            {
                SQLDescribeCol(hStmt, currentField,
                    colName, sizeof(colName), 0, 0, 0, 0, 0);
                printf(" | %s", colName);    
            }
            printf("\n");
        }
        else
        {
            printf("%s: Error: Number of fields in the result set is 0.\n", DAM);
        }

        printf("%s: Fetch the actual data:\n", DAM);
        /* Loop through the rows in the result set */
        rc = SQLFetch(hStmt);
        while (SQL_SUCCEEDED(rc)) 
        {
            printf(" | %s | %s\n", chval1, chval2);
            rc = SQLFetch(hStmt);
            rowCount++;
        };

        printf("%s: Total Row Count: %d\n", DAM, rowCount);
        rc = SQLFreeStmt(hStmt, SQL_DROP);
    }
}
else
{
    printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
}

/* Disconnect and free up allocated handles */
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

printf("%s: Cleanup. Done.\n", DAM);

MFC ODBC Example

The following example shows the connection information for MFC ODBC, which uses {Microsoft Access Driver (*.mdb, *.accdb)} driver implemented in aceodbc.dll library by including <afxdb.h>.

LPCTSTR lpszConnect = _T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\Northwind 2007.accdb;");

The following example fetches the schema and data.

// Data Access Method used in this sample.
const char* DAM = "MFC ODBC";

CDatabase db;

TRY
{
    result = db.OpenEx(lpszConnect, 
        CDatabase::openReadOnly |
        CDatabase::noOdbcDialog);
    if (FALSE == result)
    {
        cout<<DAM<<": Unable to connect to data source "<<lpszConnect<<endl;
        return result;
    }

    cout<<DAM<<": Successfully connected to database. Data source name:\n  "
        <<db.GetDatabaseName()<<endl;

    // Prepare SQL query.
    LPCTSTR query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";
    cout<<DAM<<": SQL query:\n  "<<query<<endl;
       
    // Excecute the query and create a record set.
    CRecordset rs(&db); 
    result = rs.Open(CRecordset::dynaset, query, CRecordset::none);
    if (result == TRUE)
    {
        cout<<DAM<<": Retrieve schema info for the given result set: "<<endl;
        CODBCFieldInfo fInfo; 
        short sFieldCount = rs.GetODBCFieldCount();
        if (sFieldCount > 0)
        {
            for (short nIndex=0; nIndex < sFieldCount; nIndex++)
            {
                CODBCFieldInfo fInfo;
                rs.GetODBCFieldInfo(nIndex, fInfo);
                cout<<" | "<<fInfo.m_strName;
            }
            cout<<endl;
        }
        else
        {
            cout<<DAM<<": Error: Number of fields in the result set is 0."<<endl;
        }
    
        cout<<DAM<<": Fetch the actual data: "<<endl;
        CDBVariant var;
        CString value;
       
        // Loop through the rows in the result set.
        int rowCount = 0;
        while (!rs.IsEOF())
        {
            for (short nIndex=0; nIndex < sFieldCount; nIndex++)
            {
                rs.GetFieldValue(nIndex, var);
                switch (var.m_dwType)
                {
                    case DBVT_STRING:
                        value.Format("%s", var.m_pstring->GetBuffer(var.m_pstring->GetLength()));
                        break;
                    case DBVT_ASTRING:
                        value.Format("%s", var.m_pstringA->GetBuffer(var.m_pstringA->GetLength()));
                        break;
                    case DBVT_WSTRING:
                        value.Format("%s", var.m_pstringW->GetBuffer(var.m_pstringW->GetLength()));
                        break;
                    default:
                        value = "";
                }
                cout<<" | "<<value;
            }
            cout<<endl;
            rowCount++;
            rs.MoveNext();
        }
        cout<<DAM<<": Total Row Count: "<<rowCount<<endl;
    }
}
CATCH_ALL(e)
{
    TCHAR  errMsg[255];
    e->GetErrorMessage(errMsg, 255);
    cout<<DAM<<": CException: "<<errMsg<<endl;
}
END_CATCH_ALL

db.Close();
cout<<DAM<<": Cleanup. Done."<<endl;

Performance Results

Performance results were generated by using a (x86) computer that is running Windows Server 2003 SP2, with an AMD 64 Athlon 3200+ CPU and 2 gigabyte (GB) of RAM. These results reveal a 2-3% difference in between every two neighboring data access APIs for I/O bound applications.

Remember that the benchmark programs performed heavy output to the user interface (UI) and did not use new or advanced features in the ACE engine.

The following chart visually presents these results.

Figure 2. Processing 10,000 database connections and queries with Northwind 2007 data (results in seconds)

Considerations when Choosing Data Access Technology

If you develop a solution that works with an existing Access database, you might continue to use the application's current data access technology for maintenance convenience. DAO, OLE DB, and ODBC drivers are also available for developing applications that have connectivity to all Access 2007 or earlier file formats.

However, if you expect the application to have a long life cycle, you should consider refactoring it to the DAO driver which provides the most comprehensive functionality. In the long run, the native data access technologies typically reduce development time, simplify code, and provide better performance.

The factors affecting your decision to use the most suitable data access driver include:

• Functionality. If your solution depends on the Access databases exclusively, Direct DAO should be the natural choice. If you must have advanced recordset manipulation features and are connecting to a supportive outside source, consider using OLE DB or ADO. Notice that you can mix these methods within the same project if that works best for you. Only the Direct DAO driver provides full support for work with new features that are introduced with Access 2007. The OLE DB driver has limited support for these features. For example, to enable better support for complex dataset the connection parameter "JET OLE DB: Support Complex Data" to retrieve recordsets within recordsets. Otherwise, by default, you only get delimited lists for complex fields. ADO and ODBC always get delimited lists for complex fields. To access the native behavior, Direct DAO driver provides the most comprehensive native interface to Access database. If you want to develop your solution in C, your choice might be limited to Direct ODBC approach.

• Performance. Even though ADO is fast, it does insert an additional layer of abstraction between your application and the new ACE OLE DB provider when it works with the ACE engine. In general, the direct DAO method is faster than ADO. If performance is an issue, you might write your application in Visual C++ using the OLE DB interface.

• Maintenance. To develop simple solutions, Direct DAO, and possibly ADO, are recommended. Choosing the OLE DB data access technology affects the cost of long-term maintenance for your application. OLE DB is more expensive than DAO or ADO because maintaining and improving complex COM code is more difficult. As an alternative to Direct OLE DB method, you can use ATL OLE DB approach (sample source is provided) which does a good job of abstracting the underlying COM complexity.

• 64-bit support. The Access 2007 ACE engine is a 32-bit engine. It is supported natively on 32-bit platforms. It can also be used with WOW64 technology which permits the execution of 32-bit applications on Windows x64 platforms. Although 32-bit applications can run transparently, mixing two kinds of code within the same process is not supported. An application that is written for the Windows 64-bit operating system cannot link against a 32-bit library (DLL) and similarly a 32-bit application cannot link against a 64-bit library.

Deprecated Data Access Methods

Table 2 provides a list of deprecated data access methods in Access 2007. These methods are not supported for use with Access databases saved in the .accdb file format.

Table 2. Deprecated Data Access methods

Provider	Data Access Method	Header File & Connection Info	Supported Language	Additional Comments
JET 4.0 OLE DB Provider	OLE DB	<atldbcli.h> Microsoft.JET.OLEDB.4.0	C++	Deprecated. This method does not work with new .accdb format files. You should only use it to maintain existing applications.
MFC DAO	MFC DAO	<afxdao.h>; Installed with MDAC; MFC classes that have the prefix CDao...	C++	Deprecated. This method does not work with new .accdb format files. You should only use it to maintain existing applications.
Access ODBC Driver 4.0	MFC ODBC	<afxdb.h>; odbcjt32.dll; Driver={Microsoft Access Driver (*.mdb)};DBQ=path to mdb file	C/C++	Deprecated. This method does not work with new .accdb format files. You should only use it to maintain existing applications.

Conclusion

This article describes Access, its ACE engine, and the mechanisms that are used to work with it, regardless of whether you are writing native code. It shows how to develop Access solutions in C/C++, and describes the data stack used by Access and some different components of the engine. By using data access APIs such as DAO, OLE DB, ADO, or ODBC, you can continue to create custom Access solutions for the most complex scenarios. Generally, as the default provider for the ACE engine, the Direct DAO driver provides the most comprehensive native interface to Access databases. It not only integrates well with the ACE Engine, it is also fast, stable and backward-compatible with the earlier file formats. Therefore, DAO is the recommended data access API when you develop your Access solutions.