Using Access Tables in ArcGIS Desktop

 

Like Excel, Microsoft Access is a common tabular format used to store and manage nonspatial attribute data associated with GIS features. While Excel worksheets can be accessed directly in ArcGIS, to view an Access table in ArcGIS you must connect to it using an OLE DB (Object Linking and Embedding DataBase provider) connection. Tables accessed using an OLE DB connection are read-only in ArcGIS, but can be queried, joined to layer attribute tables, and used in geoprocessing operations like other tables.

Note: A personal geodatabase (.mdb) is stored in a version of Microsoft Access. This post does not relate to personal geodatabase tables. Personal geodatabase tables should be managed and edited only in ArcGIS. If you edit a personal geodatabase table in Access, the geodatabase may become corrupt.

As a best practice, before connecting to an Access table in ArcCatalog, you should check its field name formatting to make sure ArcGIS will interpret the data correctly.

Guidelines for Access table field names:

  • Field names must start with a letter (no numbers or underscores).
  • Field names must contain only letters, numbers, or underscores (no spaces, hyphens, or special characters).
  • Field names must not contain any Microsoft reserved words.
  • Field names must be 64 characters or less.

To connect to an Access 2000 or 2002-2003 (.mdb) database:

  • In the ArcCatalog Catalog tree, expand Database Connections.
  • Double-click “Add OLE DB Connection.”
  • In the Provider tab of the Data Link Properties dialog box, click “Microsoft Jet 4.0 OLE DB Provider” in the list, then click Next.
  • In the Connection tab, browse to or enter the pathname of the database to which you want to connect, then enter your username and password if required.

Click to enlarge

  • Click Test Connection.
  • When you see the message that the test was successful, click OK.
  • Click OK again to close the Data Link Properties dialog box.
    • The connection displays in the Catalog tree with a default name of OLE DB Connection.odc. It’s a good idea to rename the connection to be more descriptive.

Database connection in Catalog tree

To connect to an Access 2007 (.accdb) database:

  • If Access 2007 is not installed on the same computer as ArcGIS, download and install the AccessDatabaseEngine.exe file from the Microsoft Web site.
  • Open ArcCatalog and expand Database Connections in the Catalog tree.
  • Double-click “Add OLE DB Connection.”
  • In the Provider tab of the Data Link Properties dialog box, click “Microsoft Office 12.0 Access Database OLE DB Provider” in the list, then click Next.
  • In the Connection tab, enter the pathname of the database to which you want to connect, then enter your username and password if required.
  • Click Test Connection.
  • When you see the message that the test was successful, click OK.
  • Click OK again to close the Data Link Properties dialog box.

After creating the OLE DB connection, you can preview Access tables in ArcCatalog and add them to ArcMap by dragging and dropping or using the Add Data tool in ArcMap. Like other nonspatial tables, Access tables display in the Source tab of the ArcMap Table of Contents.

  • Important: If you use the Add Data tool to add an Access table to ArcMap, be sure to choose Database Connections in the “Look in” drop-down list at the top of the Add Data dialog box. Adding an Access table directly to ArcMap may damage the Access database.

Click to enlarge

If you want to edit data stored in an Access table in ArcGIS, export the table from the OLE DB connection to a geodatabase table or dBASE file. The exported table will be completely separate from the Access database.

Kevin Mumford, an instructor with Esri Training Services, contributed this post.

 

SuzanneB

About SuzanneB

Suzanne is a Maryland native with a degree in English Literature who enjoys writing about Esri technology and other topics. She works with Esri Training Services in Redlands, California.
This entry was posted in ArcGIS Step by Step and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

6 Comments

  1. AriAtEvari says:

    What about the hyperlink datatype in Access? This seems to convert to text. Are hyperlinks in the format (Microsoft’s Website#http://www.microsoft.com##) available through ArcGIS Desktop?

  2. SuzanneB says:

    In ArcGIS, hyperlinks are stored in text fields. See http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Data_types_in_the_DBMS/005s0000000q000000/ for more info about ArcGIS/Access data mapping.

  3. AriAtEvari says:

    So ArcGIS doesn’t support the Microsoft Access hyperlink data type as a link only as text. I will try writing a script to convert the paths.

  4. mhansen3 says:

    Ari,

    Any success in writing that script?

  5. mimosol says:

    Hi,I’m working on connection between ArcGIS 9.3 and Access 2007 and Access 2010.Is it possible to make a connection between the two sofwares in a way that allow me to modify the attributes data (of ArcGIS files) on Access and find the data (new entities : points, lines, polygones) added on ArcGIS when I work on Access?Thank you for the help