How to Use Microsoft Access Database Engine 2010

Content feedback is currently offline for maintenance. Please try again in a few minutes.

How To: Connect to Microsoft Access 2007 / 2010 (ACCDB) files in ArcGIS

Summary

Microsoft Office 2007 introduced a new Microsoft Access database format, ACCDB. When creating a database, ACCDB is the new default format in Access 2007 and Access 2010.

            Note:            The proper way of connecting to Microsoft Access databases, either the MDB or ACCDB formats, is through an OLE DB connection. Though it is technically possible to connect to MDB files through the Add Data dialog box in ArcMap, it is not the recommended workflow. With the new Microsoft Access format, accessing ACCDB files directly through the Add Data dialog box is not possible.

Microsoft Access 2007/2010 databases can be read within the ArcGIS environments using an OLE DB database connection. Reading these databases requires installation of the correct ODBC drivers.

Procedure

ArcGIS Desktop is a 32-bit application. Because of this, it requires 32-bit ODBC drivers to make database connections. If Office 2007 or Office 2010 x86 (32-bit) is installed, the 32-bit Microsoft Access Database Engine is likely already installed. If Office 2010 x64 (64-bit) is installed (or Microsoft Office is not installed at all), the 32-bit database engine must be installed before a connection can be made to Microsoft Access databases in ArcGIS Desktop.

            Note:            It is not possible to install the 32-bit 2010 Microsoft Access Database Engine if 64-bit Microsoft Office 2010 is installed. This is a limitation as described in Microsoft Article 2269468. In this scenario, the 2007 Microsoft Access Database Engine can be installed. The 2007 engine can read both 2007 and 2010 Access databases for OLE DB purposes. For more information on 2007/2010 ACCDB compatibility, review the Microsoft Developer Network article: Backward compatibility between Access 2010 and Access 2007.

1. To check whether the 32-bit Microsoft Access Database Engine drivers are installed, go to C:\Windows\SysWOW64\odbcad32.exe. If on a 32-bit machine, go to C:\Windows\System32\odbcad32.exe to access the utility. Navigate to the drivers tab. If the Microsoft Access Driver lists .mdb and .accdb, then the correct drivers are installed. In this case, skip to Step 4.
2. If the Microsoft Access Driver only lists .mdb, the necessary driver is not installed. If 64-bit Office 2010 is installed, the 2007 Access Database Engine must be installed. Otherwise, the 2010 Access Database Engine (32-bit) can be installed.
3. After installing the engine, several new drivers appear in the ODBC Data Source Manager, including drivers that are able to read .accdb files. The Microsoft Access Driver now lists .accdb as a file type it supports.

Before and After

4. Launch the Add OLE DB Connection utility in ArcCatalog. To locate this utility using ArcGIS 10 or older, see the 10.0 Web help document Adding OLE DB connections. If using ArcGIS 10.1 or newer, see the 10.1 Web help topic Adding OLE DB connections to databases.
5. In the Data Link Properties dialog box, select the Microsoft Office 12.0 Access Database Engine OLE DB Provider.

            Note:            The Microsoft Office 12.0 Access Database Engine Provider was installed with either Microsoft Office or the Access Database Engine.          

OLE DB Provider

6. Click Next to open the Connection tab.
7. For Step 1, Data Source, either type or copy and paste the location to the ACCDB file.

Paste Path to Access Database

            Note:            The ability to browse to an ACCDB file in this dialog box is a Microsoft limitation that cannot be addressed by Esri.

8. If required by the database, enter a password.
9. Click the Test Connection button. A message appears stating whether or not the test connection was successful.
10. Click OK in the Data Link Properties dialog box.
    • There is now a new OLE DB Connection in the Database Connections folder. Optionally, the new OLE DB Connection can be renamed something that relates to or identifies the database.

Connection is listed under Database Connections

• Double-clicking the OLE DB Connection and viewing the Contents tab displays the contents of the database. Clicking the plus sign next to the database also displays the contents.
• The contents of this database can now be used in most ArcGIS dialog boxes by navigating to the Database Connections folder. Tables and queries can also be dragged and dropped from the Database Connections into ArcMap or tool dialogs.

            Note:            There are a few limitations to be aware of when working with OLE DB connections. See the link in the Related Information section below for more information.

            Note:            For NIM009080, a possible workaround is to use an Microsoft Access ODBC Driver instead of the OLE DB connection.

Related Information

• FAQ: What operations are not supported for OLE DB data sources or text files in ArcGIS?
• HowTo: Enable Select by Attribute on an OLE DB table.
• NIM009080 Double values in Access tables connected through an OLE DB connection are truncated in ArcMap.

Last Published: 6/2/2020

Article ID: 000009382

How to Use Microsoft Access Database Engine 2010

Source: https://support.esri.com/en/technical-article/000009382

Share :