Working with Microsoft Access files in ArcGIS
Release 9.3 Last modified April 24, 2009

About working with Microsoft Access files in ArcGIS

Note: This topic was updated for 9.3.1.

You can use tables from a Microsoft Access database in ArcMap through an OLE DB connection. OLE DB is a standard for sharing data between applications, enabling you to view the Access database in ArcMap. To maintain data integrity, you should only modify an Access database in Access.

Similarly, you should only edit a personal geodatabase in ArcGIS. Although a personal geodatabase is stored as an Access .mdb, you should not open or modify the tables in a personal geodatabase inside Microsoft Access. This can corrupt the geodatabase and cause data loss.

The limitation of Microsoft Access as an application to edit personal geodatabases is that Access was not designed to use or understand the ESRI functionality behind the personal geodatabase format. For example:

• The Access database format does not support geographic features.
  
• Personal geodatabases contain hidden information that Access ignores.
  
• Personal geodatabases contain multiple underlying tables that provide information for a single feature class. When editing a feature class in ArcGIS, ArcGIS keeps track of changes in all the underlying tables. Editing the geodatabase in Access may cause those tables to become out of sync and corrupt the geodatabase.
  

Microsoft Office 2007 introduced a new Microsoft Access database format, accdb, which is the new default format in Access 2007, when saving or creating a database. Without using OLE DB, ArcGIS cannot directly read or write to the new Microsoft Access format; this includes accessing accdb files directly through the Add Data dialog box.

To learn more about OLE DB connections, see Adding OLE DB connections.

How to work with Microsoft Access files in ArcGIS

Formatting a table in Microsoft Access for use in ArcGIS

1. Make sure your field names are properly formatted. You should follow these general best practices for field naming, particularly if you want to join an Access table to another table in ArcMap:
   
• Field names must start with a letter.
  
• Field names must contain only letters, numbers, and underscores.
  
• Field names must not exceed 64 characters.
  
• Field names must not contain Microsoft Access reserved words. Some examples include date, day, month, table, text, user, when, where, year, and zone. For a list of reserved words, see the following Microsoft support article (KB 286335).
  


2. If you import a table into a database in Access, follow these guidelines when using the wizard:
   
• The fields do not need to be indexed.
  
• If the table does not have a field with unique values, allow Access to add a primary key.
  
• Name the table with only letters, numbers, or underscores and begin it with a letter.
  

Connecting to a Microsoft Access database in ArcGIS

1. In ArcCatalog, double-click the Database Connections folder.
   
2. Double-click Add OLE DB Connections.
   
3. Click the Provider tab.
   
4. Click Microsoft Jet 4.0 OLE DB Provider.
   
5. Click Next.
   
6. On the Connection tab, specify the database or browse to it in section one. If the database has a password, enter that information in section two.
   
7. Click Test Connection to verify that you can connect to the database.
   
8. Click OK if the connection test was successful.
   
9. Type a new name for the connection and press Enter.
   
10. To use the Access table, browse to the table through the OLE DB connection and add it to ArcMap.
    
Access tables, like other tables without associated features, only show up on the Source tab of the ArcMap table of contents.

Tips

Because OLE DB connections do not have an ObjectID field, which is a field maintained by ArcGIS that guarantees a unique ID for each row in the table, you won't be able to perform certain operations in ArcMap. These include selecting the features in the layer on the map in any way, applying a definition query, creating full relates, or editing the attributes. See About ObjectID fields for more information about working with tables that lack Object IDs. Linked tables in Access are not viewable through an OLE DB connection, although Access queries are available. To use the linked table, create a query in Access that references the linked table and connect to the linked table through the query in the OLE DB connection.

Connecting to a 2007 Microsoft Access database (.accdb) in ArcGIS

1. If Microsoft Office 2007 is installed on the same machine as ArcGIS, skip to step 2. If attempting to connect to an accdb file on a machine that does not have Microsoft Office 2007, download the AccessDatabaseEngine.exe from 2007 Office System Driver: Data Connectivity Components.
   
2. In ArcCatalog, double-click the Database Connections folder.
   
3. Double-click Add OLE DB Connections.
   
4. Click the Provider tab.
   
5. Click Microsoft Office 12.0 Access Database Engine OLE DB Provider.
   
6. Click Next.
   
7. On the Connection tab, specify the database or browse to it in section one. If the database has a password, enter that information in section two.
   
8. Click Test Connection to verify that you can connect to the database.
   
9. Click OK if the connection test was successful.
   
10. Type a new name for the connection and press Enter.
    
11. To use the Access table, browse to the table through the OLE DB connection and add it to ArcMap.
    

Tips

Because OLE DB connections do not have an ObjectID field, which is a field maintained by ArcGIS that guarantees a unique ID for each row in the table, you won't be able to perform certain operations in ArcMap. These include selecting the features in the layer on the map in any way, applying a definition query, creating full relates, or editing the attributes. See About ObjectID fields for more information about working with tables that lack Object IDs. Linked tables in Access are not viewable through an OLE DB connection, although Access queries are available. To use the linked table, create a query in Access that references the linked table and connect to the linked table through the query in the OLE DB connection.

Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.