Knowledge Base - Technical Articles


Technical Article   HowTo:  Connect to Microsoft Access 2007/2010 (ACCDB) files in ArcGIS

Article ID: 32976
Software:  ArcGIS - ArcEditor 9.2, 9.3, 9.3.1, 10 ArcGIS - ArcInfo 9.2, 9.3, 9.3.1, 10 ArcGIS - ArcView 9.2, 9.3, 9.3.1, 10 ArcGIS for Desktop Advanced 10.1 ArcGIS for Desktop Standard 10.1 ArcGIS for Desktop Basic 10.1
Platforms:  Windows 2000, XP, Server 2003, Vista, Server 2008, Windows 7, Windows 8, Server 2012

Summary

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

 The proper way of connecting to Microsoft Access databases, either the MDB or ACCDB formats, is through an OLEDB 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 the correct ODBC drivers to be installed.

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 Access databases in ArcGIS Desktop.

 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 Microsoft 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 MSDN 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. Go 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. Else, 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. -show me-


Before and After
[O-Image]

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.

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

-show me-

OLE DB Provider
[O-Image]

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.

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

-show me-

Paste Path to Access Database
[O-Image]

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. -show me-


Connection will be under Database Connections
[O-Image]

• Double-clicking the OLEDB 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.



Created: 4/27/2007
Last Modified: 7/22/2013

Article Rating: (6)
If you would like to post a comment, please login

Comments

By Hornbydd - 03/13/2013 10:28 AM

The article needs to be updated.

The additional information for connecting ArcMap 10.1 to an Access Database has been very useful but I have two comments. 1) There should be a step 11, once the new OLE DB connection has been added in ArcCatalog the user will need to refresh the Database Connections folder to actually see the connection in ArcCatalog (or close and open the application). Maybe the the add OLE DB button needs the code behind it to force a refresh? 2) The very last statement says you can drag a table into ArcMap. If I drag from ArcCatalog to the map/TOC it crashes. If I drag a table from the Catalog window in ArcMap to the map/TOC it crashes. In fact any attempt to drag and drop that table into ArcMap it crashes... The only way to open that table in ArcMap and start using it is to go via the Add data button route. So maybe something ESRI could looking into?

Rating:

By dk10 - 12/05/2011 4:45 PM

The article needs to be updated.

I am able to connect to see the tables within the AccDB file - but does anyone know how to write from a FileGDB to the AccDB file - Aspatial Table of course. Thanks

Rating:

By kevi3032 - 08/01/2011 5:22 AM

Great article! It helped a lot!

I definately needed the 'show me' part, when filling in the location. It ended up being easy, but I wasn't sure based on previous OLE DB connections I've done w/ AGIS in the past

Rating:

By Anonymous - 02/18/2011 3:22 AM

I’d like to suggest some keywords to help find this article.

I can connect to a 2007 .accdb with this if the Access database has a username AND password assigned or if there's no database password. However how do I connect to a database if it's password protected (using the Access 2007 Encrypt Database). I've tried leaving the username blank and just enter a password, but no luck.

Rating:

By Anonymous - 08/25/2010 10:15 AM

The article needs to be updated.

This worked very well. Strait to the point. Step by step directions. Very helpful. Thank you.

Rating:

By Anonymous - 07/28/2008 7:13 AM

The article needs to be updated.

The article claims to be for Windows 2000, but the download mentioned in point 1 will not install on anything less than XP or 2003.

By Anonymous - 06/11/2008 2:45 PM

Great article! It helped a lot!

Thanks, for having this out there. Otherwise I would be stuck with a 2007 Access database.

Rating: