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: Microsoft Access 2010 | Microsoft Office Access 2007
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
- Additional Resources
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.
Note:
When referring to Access in this document and not a specific version of Microsoft Access (such as Microsoft Access 2003) it is assumed that the information applies to both Microsoft Office Access 2007 and Microsoft Access 2010.
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 follo
wing 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
Note:
The Microsoft Access Database Engine 2007 and 2010 drivers are released to enable solution developers to build tools to read and write to Office files such as .accdb, .xlsx, and .xlsb. It allows a solution builder to have a server (such as SQL) to read and write Office file formats without requiring that they install Office on the server. However, this driver is not licensed for use as a stand-alone data store.
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
|
Provider Name |
Data Access Method |
Connection information |
Supported Language(s) |
|
ACE DAO |
Acedao.tlh (generated from acedao.dll); |
C++ |
|
|
acedao.dll |
|||
|
Set db = CurrentDb() |
VBA |
||
|
Runs in VBE environment |
|||
|
ACE OLE DB |
Microsoft.ACE.OLEDB.12.0 |
C++ |
|
|
<Atldbcli.h> and <Atldbsch.h>; Aceoledb.dll |
|||
|
ADO.NET |
Microsoft.ACE.OLEDB.12.0 |
C# |
|
|
using System.Data.OleDb; |
|||
|
Microsoft.ACE.OLEDB.12.0 |
Visual Basic.NET |
||
|
Imports System.Data.OleDb |
|||
|
Imports System.Console |
|||
|
ADO |
Msado15.tlh (generated from Msado15.dll); |
C++ |
|
|
Installed with MDAC 2.8 or Windows DAC 6.0. |
|||
|
ACE ODBC |
Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file |
C/C++ |
|
|
<Sqlext.h>; |
|||
|
Aceodbc.dll; |
|||
|
Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path of mdb/accdb file |
C++ |
||
|
<Afxdb.h>; |
|||
|
Aceodbc.dll; |
|||
|
jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ= path of mdb/accdb file |
Java |
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.
No code example is currently available or this language may not be supported.
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_MACHINESOFTWAREMicrosoftOffice14.0Access Connectivity EngineInstallRoot
- HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineInstallRoot
Because it connects directly to the engine, only the database location is sufficient for the connection information.
No code example is currently available or this language may not be supported.
The following code example gets the schema and data.
Note:
To access the complete sample applications, download the sample code.
No code example is currently available or this language may not be supported.
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.
No code example is currently available or this language may not be supported.
The following code example gets the schema and data.
No code example is currently available or this language may not be supported.
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.
No code example is currently available or this language may not be supported.
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.
No code example is currently available or this language may not be supported.
For connection information, use the Microsoft.ACE.OLEDB.12.0 data provider implemented in Aceoledb.dll.
No code example is currently available or this language may not be supported.
The following code example gets the schema and data.
No code example is currently available or this language may not be supported.
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>.
No code example is currently available or this language may not be supported.
The following code example gets the schema and data.
No code example is currently available or this language may not be supported.
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>.
No code example is currently available or this language may not be supported.
The following code example gets the schema and data.
No code example is currently available or this language may not be supported.
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)
Note:
All benchmark programs were run as console applications except VBA DAO code which was run from the VBA/VBE environment. This means that VBA code was the only program that had a privilege of executing within the same address space of MSAccess.exe which resulted in a significant reduction of Disk I/O operations and page faults improving the overall performance.
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:
- Deploy 64-bit Access 2010 on 64-bit Windows
- 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.
Important:
You will get run-time errors if you try running your legacy 32-bit code against 64-bit Access. For example, the “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local computer” error might be caused by a version mismatch between your application (32-bit code) and one of the 64-bit ACE providers that were installed with 64-bit Microsoft Access. To correct this issue, either upgrade your custom code to 64-bit version or uninstall 64-bit Access and replace it with 32-bit Access.
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.
Note:
Office Access 2007 ACE providers work only with 32-bit client code. Access 2010 ACE providers support either 32-bit or 64-bit code. To implement a 64-bit solution, both you and your clients must deploy 64-bit a Access 2010 product. For more information, see 32-bit vs. 64-bit ACE Providers.
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
|
Provider Name |
Data Access Method |
Connection and other information |
Supported Language(s) |
|
JET4.0 OLE DB Provider |
OLE DB |
Microsoft.JET.OLEDB.4.0 |
C++ |
|
<Atldbcli.h> |
|||
|
MFC DAO |
MFC DAO |
<Afxdao.h>; |
C++ |
|
Installed with MDAC; MFC classes that have the prefix CDao. |
|||
|
The MFC DAO classes give the user a way to use the legacy Microsoft JET Database Engine. However, they are not supported as of Access 2007. DAO 3.6 is the final version of this technology. It is not available on the 64-bit Windows. Visual C++ .NET wizards do not generate code that automatically creates and opens recordsets. |
|||
|
Access ODBC Driver 4.0 |
MFC ODBC |
Driver={Microsoft Access Driver (*.mdb)};DBQ=path of mdb file |
C++ |
|
<Afxdb.h>; |
|||
|
Odbcjt32.dll; |
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.
Additional Resources
For more information, see the following resources:
