Data Programming with Microsoft Access 2010

Summary:  Learn how to develop either native (C, C++, Java, VBA) or managed (C#, Visual Basic.NET) data access code with Microsoft Office Access 2007 or Microsoft Access 2010. Learn about the Access architecture, the ACE engine and data providers, 32-bit and 64-bit platforms, and what things to consider when you choose an optimal data access technology for your new or legacy database project.

Applies to: Access 2007 | Access 2010 | Office 2010

In this article
Overview
Architecture of the Microsoft Access Engine
Overview of Data Access Technologies
Performance Data
Using 32-bit and 64-bit ACE Providers
Considerations when Choosing Data Access Technology
Deprecated Data Access Methods
Conclusion

Published:  September 2010

Provided by:  Aleksandar Jakšić, Microsoft Corporation

Contents

• Overview

• Architecture of the Microsoft Access Engine

• Overview of Data Access Technologies

• Performance Data

• Using 32-bit and 64-bit ACE Providers

• Considerations when Choosing Data Access Technology

• Deprecated Data Access Methods

• Conclusion

  Download code

Overview

This technical article explores the many data access programmability points of Access 2007 and Access 2010. It discusses the Access engine, which is known as the Microsoft Access Database Engine (ACE Engine). If you are a database developer and you want to know more about the options that are available to you when you are developing a data access solution for Access databases, this article is written for you. This article does not attempt to give you details for every data access interface mentioned here. Instead, its intention is to give you an architectural overview of the ACE engine so you can better understand the programming model that is built that is running on it. The accompanying sample code is written in both native (C, C++, Java, and VBA) and the .NET Framework (C# and Visual Basic.NET) languages, and is intended to help you get started quickly, even if you have never programmed in some of these languages before.

This technical article contains answers to questions such as the following:

• Is my legacy 32-bit application going to work with Access 64-bit software?

• What is 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) or even Java?

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

Code Examples

This technical article presents nine complete programs created by using Microsoft Visual Studio 2008 and are available for download.

Each of the code examples performs the same data access algorithm using different programming languages and variety of data access technologies such as are DAO, OLE DB, ADO.NET, ADO, ODBC, or JDBC. The console output of each program is almost identical. Examples demonstrate core data access programming functionality such as building up a connection string with a strong password for decrypting a database, connecting to an encrypted database, building up and executing an SQL query, working with a schema and recordset, and retrieving sorted data.

Because the Access engine is not part of the Windows operating system, you must install the ACE Engine and its providers on your local computer. For more information, see Where can I get the ACE Engine?.

This technical article also presents Performance Data for all data providers included in the code samples.

Who This Technical Article Is For

The subject of data access technologies is large and because this guide is directed toward a broad developer audience, any developer who works with Access data at the business logic or the application level might benefit from this material. However, this technical article is also written with the idea of reaching out to IT workers, students, or enthusiasts who might be more interested generally database programming.

It is assumed that you are comfortable with the Visual Studio environment, basics of (structural and the .NET Framework) programming, and are familiar with the concept of relational databases and SQL. It is also assumed that you are comfortable creating tables and queries with any prior release of Microsoft Access.

Architecture of the Microsoft Access Engine

Figure 1 shows how Access (UI) and ACE (engine) form a complete database management system (DBMS).

Figure 1. Conceptual view of Access 2010

Access UI is responsible for the user interface and all the ways that users view, edit, and use data through forms, reports, queries, macros, wizards and so on. On the other hand, the Microsoft Access Engine (ACE Engine) provides core database management services such as the following:

• Data storage─Store data in the file system.

• Data definition─Create, edit, or delete structures for holding data such as tables and fields.

• Data integrity─Enforce relational rules that prevent data corruption.

• Data manipulation─Add, edit, delete, or sort existing data.

• Data retrieval─Retrieve data from the system using SQL.

• Data encryption─Protect data against unauthorized use.

• Data sharing─Share data in a multiuser network environment.

• Data publishing─Work in a client or server web environment.

• Data importing, exporting, and linking─Work with data from different sources.

From the data access perspective, you can view Access as the way the ACE engine is exposed graphically to users.

Where can I get the ACE Engine?

To run the code samples provided in this technical article must have the ACE engine on your computer which means that you must install one of the following Access 2010 (or Office Access 2007) products:

• Microsoft Access 2010─Available in the following Office 2010 Editions: Professional, Professional Academic, Professional Plus, or Microsoft Access stand-alone

• Microsoft Access 2010 Runtime─Available as a free download

• Microsoft Access Database Engine 2010 Redistributable

What about the Microsoft JET Engine?

Prior to Access 2007, Access used the Microsoft Joint Engine Technology (JET) engine. Even though JET was generally seen as part of Access, the JET engine used to be a separate product. Since Microsoft Windows 2000 release, JET was included as part of the Windows operating system and then distributed or updated with the Microsoft Data Access Components (MDAC). However, with Access 2007 release the JET engine was deprecated and is no longer distributed with MDAC. Instead, Access now uses an integrated and improved ACE engine whose development started by taking a code snapshot of the original JET code base.

The ACE engine is fully backward-compatible with previous versions of the JET engine so that it reads and writes (.mdb) files from earlier Access versions. Because the Access team now owns the engine, developers can be confident that their Access solutions will not only continue to work in the future but will also be faster, more robust, and feature rich. For example, with Access 2010 release, among other improvements, the ACE engine was upgraded to support 64-bit version and to enhance the integration with SharePoint related technologies and Web services generally. Microsoft is committed to Access as a developer platform.

Overview of Data Access Technologies

Microsoft provides several ways to work with Access databases. The following data access APIs and data access layers are used for Access programming:

• Data Access Objects (DAO)

• Object Linking and Embedding, Database (OLE DB)

• ADO.NET

• ActiveX Data Objects (ADO)

• Open Database Connectivity (ODBC)

ACE engine implements providers for the three technologies mentioned earlier: DAO, OLE DB and ODBC. The ACE DAO provider, the ACE OLE DB provider and the ACE ODBC provider are distributed with Access product (except ADO which is still part of the Microsoft Windows DAC). Many other data access programming interfaces, providers and system-level frameworks that include ADO and ADO.NET, are built on those three ACE providers. Figure 2 shows a diagram that represents an overview of the Access components.

Figure 2. Architecture of the ACE engine in the data access programming environment

This technical article documents the data access methods listed in the following table.

Table 1. Data Access Methods

For more information and a list of data access methods that are deprecated with Access 2007 release, see Deprecated Data Access Methods.

DAO

Initially, DAO was the exclusive data access method for Access developers.

Direct DAO Example

This access method provides the best support for new features that are introduced with Access 2007 because it exposes most of the ACE engine functionality. 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 code example.

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

Depending on whether you are compiling code on 32-bit or 64-bit operating system, you might want to update this path to remove the "(x86)" part. One way to verify this is to check a registry key named Path of type REG_SZ under either of the following registry paths:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\InstallRoot

• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\InstallRoot

Because it connects directly to the engine, only the database location is sufficient for the connection information.

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

The following code example gets 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 C++ try/catch block
    try
    {
        DAO::DatabasePtr pDbPtr = NULL;
        pDbPtr = pEngine->OpenDatabase(bstrConnect, false, false, ";PWD=1L0v3Acce55;");
        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 ORDER BY Customers.[Company] ASC;";
            cout<<DAM<<": SQL query:\n  "<<query<<endl;

            // Run 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 column = 0; column < pFields->Count; column++)
                    {
                        cout<<" | "<<pFields->GetItem(column)->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 column = 0; column < pFields->Count; column++)
                    {
                        cout<<" | "<<_bstr_t(pFields->GetItem(column)->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.ErrorMessage()<<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.accdb
Direct DAO: SQL query:
  SELECT Customers.[Company], Customers.[First Name] FROM Customers ORDER BY Customers.[Company] ASC;
Direct DAO: Retrieve schema info for the given result set:
 | Company | First Name
Direct DAO: Fetch the actual data:
 | Company A | Anna
 | Company AA | Karen
 | Company B | Antonio
 | Company BB | Amritansh
 | Company C | Thomas
 | Company CC | Soo Jung
 | 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
Direct DAO: Total Row Count: 29
Direct DAO: Cleanup. Done.

VBA DAO Example

The following code example runs in Access VBA/VBE environment, on a current database.

    Public Sub VBADAO()
        Dim DAM As String
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Dim query As String
            
        DAM = "VBA DAO"
    
        ' Open pointer to current database
        Set db = CurrentDb()
       
        Debug.Print DAM & ": Successfully connected to database. Data source name: " & _
            vbNewLine & "  " & db.Name
        
        ' Prepare SQL query
        query = "SELECT Customers.[Company], Customers.[First Name] " & _
            "FROM Customers " & _
            "ORDER BY Customers.[Company] ASC"
            
        Debug.Print DAM & ": SQL Query: " & _
            vbNewLine & "  " & query
    
        ' Run the query and create a record set
        Set rst = db.OpenRecordset(query)
        
        Debug.Print DAM & ": Retrieve schema info for the given result set: "
        For i = 0 To rst.Fields.Count - 1
            Debug.Print " | " & rst.Fields(i).Name
        Next i
            
        Debug.Print DAM & ": Fetch the actual data: "
        Do While Not rst.EOF
            Debug.Print " | " & rst![Company] & " | " & rst![First Name]
            rst.MoveNext
        Loop
        
        Debug.Print DAM & ": Total Row Count: " & rst.RecordCount
        Debug.Print DAM & ": Cleanup. Done. "
    
        rst.Close
        db.Close
    End Sub

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 ADO.NET. OLE DB specifies a set of 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 development requirements 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.accdb;Jet OLEDB:Database Password=1L0v3Acce55;";

The following code example gets 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 the ATL 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 ORDER BY Customers.[Company] ASC;";
            cout<<DAM<<": SQL query:\n  "<<OLE2T(query)<<endl;

            // Run 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.NET

ADO.NET provides uniform and comprehensive data access to various data sources within .NET (managed) environment. ADO.NET uses .NET managed providers which in turn use underlying APIs such are OLE DB and ODBC. This technical article presents two ADO.NET examples (C# and Visual Basic.NET) both using underlying OLE DB as data access layer.

C# ADO.NET Example

The following is a C# ADO.NET code example.

// Connection string for ADO.NET via OleDB
OleDbConnection cn = 
    new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Northwind.accdb;Jet OLEDB:Database Password=1L0v3Acce55;");

// Prepare SQL query
string query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers ORDER BY Customers.[Company] ASC;";
OleDbCommand cmd = new OleDbCommand(query, cn);

try
{
    cn.Open();
    Console.WriteLine("{0}: Successfully connected to database. Data source name:\n {1}", 
        DAM, cn.DataSource);
    Console.WriteLine("{0}: SQL query:\n {1}", DAM, query);

    // Run the query and create a record set
    OleDbDataReader dr = cmd.ExecuteReader();
    Console.WriteLine("{0}: Retrieve schema info for the given result set:", DAM);
    for (int column = 0; column < dr.FieldCount; column++)
    {
        Console.Write(" | {0}", dr.GetName(column));
    }
    Console.WriteLine("\n{0}: Fetch the actual data: ", DAM);
    int row = 0;
    while (dr.Read())
    {
        Console.WriteLine(" | {0} | {1} ", dr.GetValue(0), dr.GetValue(1));
        row++;
    }
    Console.WriteLine("{0}: Total Row Count: {1}", DAM, row);
    dr.Close();
}
catch (OleDbException ex)
{
    Console.WriteLine("{0}: OleDbException: Unable to connect or retrieve data from data source: {1}.",
        DAM, ex.ToString());
}
catch (Exception ex)
{
    Console.WriteLine("{0}: Exception: Unable to connect or retrieve data from data source: {1}.",
        DAM, ex.ToString());
}
finally
{
    cn.Close();
    Console.WriteLine("{0}: Cleanup. Done.", DAM);
}

Visual Basic.NET ADO.NET Example

The following is a Visual Basic.NET ADO.NET code example.

' Connection string for ADO.NET via OleDB
Dim cn As OleDbConnection = 
    New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Northwind.accdb;Jet OLEDB:Database Password=1L0v3Acce55;")
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader

Try
    cn.Open()
    WriteLine(DAM + ": : Successfully connected to database. Data source name:" + ControlChars.Lf + "  " + cn.DataSource)
    ' Prepare SQL query.
    Dim query As String = "SELECT Customers.[Company], Customers.[First Name] FROM Customers ORDER BY Customers.[Company] ASC;"
    WriteLine(DAM + ": SQL Query:" + ControlChars.Lf + "  " + query)

    ' Run the query and create a record set
    cmd = New OleDbCommand(query, cn)
    dr = cmd.ExecuteReader
    WriteLine(DAM + ": Retrieve schema info for the given result set:  ")
    Dim column, row As Integer
    For column = 0 To dr.FieldCount - 1
        Write(" | " + dr.GetName(column))
    Next column
    WriteLine(ControlChars.Lf + DAM + ": Fetch the actual data:  ")
    row = 0
    While dr.Read()
        WriteLine(" | " + dr(0) + " | " + dr(1))
        row += 1
    End While
    WriteLine(DAM + ": Total Row Count: " + row.ToString())
    dr.Close()
Catch ex As OleDbException
    WriteLine(ControlChars.Lf + DAM + ": OleDbException: Unable to connect or retrieve data from data source: " + ex.Message())
Catch ex As Exception
    WriteLine(ControlChars.Lf + DAM + ": Exception: Unable to connect or retrieve data from data source: " + ex.Message())
Finally
    cn.Close()
    WriteLine(DAM + ": Cleanup. Done.")
End Try

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.

Unlike DAO, which exposed functionality of a single database engine, ADO uses a common programming model to access data universally.

ADO Example

ADO uses the Msado15.dll library, which is included with MDAC 2.8 or later. To compile this code, you must use #import macro to generate .tlh header by specifying directive similar to the one shown in the following code 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.accdb;Jet OLEDB:Database Password=1L0v3Acce55;";

The following code example gets the schema and data.

// COM errors are handled by C++ try/catch block
try
{
    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 ORDER BY Customers.[Company] ASC;";
        cout<<DAM<<": SQL query:\n  "<<query<<endl;

        // Run 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;
    }
}
catch(_com_error& e)
{
    cout<<DAM<<": _com_error: "<<e.Description()<<endl;
}

ODBC

ODBC (Open Database Connectivity) is the oldest of the current data access technologies 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-like API.

Direct ODBC Example

This data access method is recommended only for maintenance of the existing applications or when you must work with ANSI/ISO C. The following code 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>.

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

The following code example gets the schema and data.

HENV    hEnv;
HDBC    hDbc;

/* ODBC API return status */
SQLRETURN  rc;

SQLSMALLINT  iConnStrLength2Ptr;
SQLCHAR      szConnStrOut[255];

SQLCHAR* query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers ORDER BY Customers.[Company] ASC;";

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

/* Number of rows and columns in result set */
SQLINTEGER      rowCount = 0;
SQLSMALLINT     fieldCount = 0, column = 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, szDSN,  _countof(szDSN), 
szConnStrOut, 255, &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, &ret1);
    rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, &ret2);
   
    /* Run 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 (column = 1; column <= fieldCount; column++)
            {
                SQLDescribeCol(hStmt, column,
                    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 code example shows the connection information for MFC ODBC, which uses {Microsoft Access Driver (*.mdb, *.accdb)} driver implemented in the Aceodbc.dll library by including <Afxdb.h>.

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

The following code example gets the schema and data.

BOOL result = TRUE;
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 ORDER BY Customers.[Company] ASC;";
    cout<<DAM<<": SQL query:\n  "<<query<<endl;
       
    // Run 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;

JDBC-ODBC Example

A JDBC is a data access layer that enables Java to interact with underlying data source. JDBC-ODBC bridge is a database driver implementation that relies on the ODBC driver to connect to Access database. The driver converts JDBC method calls into ODBC function calls.

private static String strConnect =
"jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=C:\\Northwind.accdb;PWD=1L0v3Acce55;";

The following Java code example gets the schema and data.

try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection con = DriverManager.getConnection(strConnect, "",""); 
   if (null == con) {
   System.out.println(DAM + "Unable to connect to data source " + strConnect);
   return;
   }
   
   System.out.println(DAM + ": Successfully connected to database. Data source name:\n  " 
+ con.getMetaData().getURL());
  
   // Prepare SQL query.
   String query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers ORDER BY Customers.[Company] ASC;";
   System.out.println(DAM + ": SQL query:\n " + query);
   
   // Run the query and create a record set
   Statement stmt = con.createStatement();
   stmt.execute(query); 
   ResultSet rs = stmt.getResultSet(); 
   if (rs != null) {
   System.out.println(DAM + ": Retrieve schema info for the given result set: ");
   ResultSetMetaData rsmd = rs.getMetaData();
   for (int i=1; i <= rsmd.getColumnCount(); i++) {
   System.out.print(" | " + rsmd.getColumnName(i));
   }
   
   System.out.println("\n" + DAM + ": Fetch the actual data: ");
   int rowCount = 0;
   while (rs.next()) {
   for (int i=1; i <= rsmd.getColumnCount(); i++) {
       System.out.print(" | " + rs.getString(i));
       }
   System.out.println("");
   rowCount++;
       }
   System.out.println(DAM + ": Total Row Count: " + rowCount);
   }
   stmt.close(); 
   con.close(); 
} catch (Exception err) {
   System.out.println(DAM + ": Exception: " + err.getMessage());
} finally {
   System.out.println(DAM + ": Cleanup. Done.");
}

Performance Data

Performance results were generated for 64-bit Access 2010 on a 64-bit operating system that is running Windows Server 2008 SP2, with an AMD 64 Athlon X2 Dual Core Processor 4600+ 2.40 GHz CPU and 8 GB RAM. The benchmark programs did not output to the user interface (UI) and did not use new or advanced features in the ACE engine.

Figure 3 shows a chart that presents performance data.

Figure 3. Performance measurements (in seconds)

Using 32-bit and 64-bit ACE Providers

The ACE providers (ACE DAO, ACE OLE DB or ACE ODBC) for Access 2007 product are available only in 32-bit. The ACE providers for Access 2010 product are available in both 32-bit and 64-bit editions.

Basically, there are three possible configurations now.

64-bit Only Solution (64-bit Access, 64-bit Windows)

To implement a 64-bit solution you must do the following:

1. Deploy 64-bit Access 2010 on 64-bit Windows

2. Build a custom 64-bit data access application

32-bit Only Solution (32-bit Access, 32-bit Windows)

If you have a 32-bit application, and want it to continue to run unchanged with Access 2010, you must install the 32-bit version of Access 2010.

32-bit Access 2010 works exactly like 32-bit Access 2007 does, and no changes will be required for your VBA code, COM Add-ins or ActiveX controls to continue to function.

WOW64 Solution (32-bit Access, 64-bit Windows)

WOW64 technology permits the execution of 32-bit applications on Windows 64-bit platforms. It is possible to install 32-bit Access 2010 on 64-bit Windows. In that case, your data application has to be 32-bit to be able to talk to the ACE providers. This is the default installation on 64-bit Windows operating systems, which allows for compatibility with 32-bit Office applications.

Although 32-bit applications can run transparently, mixing two kinds of code within the same process is not supported. A 64-bit application cannot link against a 32-bit system library (DLL) and similarly a 32-bit application cannot link against a 64-bit system library.

Figure 4. Matching versions between ACE Providers and applications

Side-by-side Installations

No support is provided for side-by-side installations of 64-bit and 32-bit editions of Office 2010. This includes Access.

Other Considerations

Before you deploy 64-bit Access, determine whether it is an appropriate deployment option for your specific environment. There are several areas that might affect the compatibility of your current 32-bit Access solution. For example, if you use databases that have had their source code removed (such as .mde, .ade, and .accde files) or if you use VBA code with Declare statements, COM add-ins, or ActiveX controls, you might have to invest development time before this functionality can work with 64-bit Access. Alternative workaround for this issue might be to install 32-bit Access on 32-bit Windows or 32-bit Access (WOW64) on 64-bit Windows. For more information about this topic, see 64-bit editions of Office 2010.

Considerations when Choosing Data Access Technology

If you developed a solution that works with an existing Access database, you might want to continue using the application's current data access technology as long as it meets your requirements. The Deprecated Data Access Methods section lists drivers that continue to provide connectivity to legacy Access file formats. Be aware that all new ACE providers are also fully backward compatible.

If you expect the application to have a long life cycle, consider several factors during the requirements analysis and design. Some factors that affect your decision to use the most suitable data access driver might include the following:

• Language/Platform. Are you limited to implementing your solution with native languages (C, C++, VBA) vs. managed (C#, Visual Basic.NET) languages? Managed languages and their underlying data access framework (ADO.NET) offer easier implementation, better platform interoperability and scalable data access. For example, if you want great integration with the .NET Framework, XML, solution that requires the disconnected business logic (in-memory presentation of relational data) or well-defined, factored interfaces with predictable behavior, performance, and semantics, the choice of ADO.NET might be perfect for you. On the other hand, if you want the best possible performance or are limited to use of a specific language that you might be looking at other methods also (Direct DAO, Direct OLE DB, and so on). If you want to develop your solution in C, your choice might be limited to Direct ODBC approach. Similarly, for Java development, you might want to use JDBC-ODBC driver.

• Functionality. If your solution depends on the Access databases exclusively, and it will stay like that, the ACE DAO driver should be the natural choice because it provides the most comprehensive functionality. In the long run, the native data access technologies typically reduce development time, simplify code, and provide better performance. If you must have advanced recordset manipulation features and are connecting to a supportive outside source, consider using ADO.NET (or ADO) or OLE DB. Only the ACE DAO driver provides full support for legacy features such as linked tables and saved queries and for new complex data types that are introduced with Access 2007. The ACE OLE DB driver has limited support for complex data. For example, to enable better support for complex dataset (to retrieve recordsets within recordsets), set the connection parameter "JET OLE DB: Support Complex Data". Otherwise, by default, you only get delimited lists for complex fields. ADO.NET, ADO and ACE ODBC always get delimited lists for complex fields.

• Security. Writing secure database code in a multiuser and web environment is much more than creating a strong encryption password. An application that accesses a database has many potential points of failure that an attacker can exploit to retrieve, manipulate, or destroy sensitive data. It is therefore important to understand all aspects of security, from threat modeling during the design phase of your application, to its eventual deployment and ongoing maintenance. In general, the .NET Framework provides easier to use and well integrated environment for improving the security of your application.

• Performance. Even though ADO.NET and ADO are fast, they do insert an additional layer of abstraction between your application and the ACE OLE DB provider when it works with the ACE engine. In general, the direct DAO, OLE DB and ODBC methods are fastest especially for larger databases. If performance is an issue, and you expect your database to increase significantly over time, you might write your application in C++ using the OLE DB or DAO interface.

• Maintenance. To develop simple solutions, ADO.NET or Direct DAO (and possibly ADO), see comment. 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.NET 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.

Deprecated Data Access Methods

Table 2 lists deprecated data access methods as of Access 2007. These methods are not supported for use with Access databases saved in the .accdb file format, and should be only used to maintain legacy applications.

Table 2. Deprecated Data Access Methods

Conclusion

This technical article lays out the high-level architecture of Microsoft Access, its ACE engine and data providers. It explores various data access technologies that are available at your disposal for programming Access, regardless of whether you are developing native or managed code, 32-bit or 64-bit. By using data access technologies such are DAO, OLE DB, ADO.NET, ADO, ODBC, or JDBC you can continue to create custom Access solutions even for the most complex and demanding scenarios. Generally, as the default provider for the ACE engine, the ACE DAO driver provides the most comprehensive native interface to Access databases, and ADO.NET is good alternative for .NET languages. They both not only integrate well with the ACE Engine, but also provide fast, stable and backward-compatible environment with legacy file formats.