Overview

There are several reasons you may want to be able to keep data in a relational database program rather than in a GIS program. A relational database program is generally much more efficient and flexible for maintaining , modifying, and querying tabular attribute data than a GIS program.  Often tabular data in a database is updated continually by the responsible organization, and you may want to link to this data.  Data from a tabular relational database can be brought into ArcView (and most other GIS software programs) in at least two ways:

• Databases in .dbf format can be imported into ArcView from any source that supports the .dbf format

• Databases can be queried "live" using SQL and ODBC capabilities - this method may be preferable in situations where data is continually being updated by another department or organization.

This page describes the second method: live-linking ArcView to a Microsoft Access database.

The Basic SQL Connect Process:

Assuming you have an Access database (including tables or queries), follow these steps to connect to it from ArcView:

1. Make sure that any fields in Access (including fields in queries) which you plan to query using ArcView have dBase-compatible field names: 10 characters maximum in length, no spaces, no odd characters, etc.  Also do not use hyphens!  If these field names are not dBase compliant, you will get an error message when trying to connect from ArcView.

2. In the ArcView Project Menu, choose Project-SQL Connect

3. You are then presented with the SQL Connect form (the example form below has been completely filled out to query an access database containing water quality monitoring information  - step by step instructions are given below the form)   
   

4. In the SQL Connect dialog box, specify your "Connection"  as "ms-access" (if this isn't an option, see note below on using the ODBC Manager)

5. Click on the CONNECT button

6. In the file dialog box that appears, navigate to where your Access database is stored and select that file (.mdb)

7. If the connection is made, you will see all the available tables and queries in the left hand side of the SQL Connect form.

8. Choose the table or query to which you wish to connect by clicking on its name -this places the available field list on the right side of the form

9. Choose the fields you want in your query by double-clicking on each one - this places the field name in the SELECT window of the form

10. Fill out the FROM part of the form by double-clicking on the table or query name

11. You can write criteria in the WHERE window, but it is generally easier to do this in your Access query form than here (so leave it blank)

12. Under OUTPUT TABLE, there will be a default name  (e.g., table1) - you can change this to whatever you wish; the name you specify here will be the table's name once it is brought into ArcView

13. Click on QUERY to run the query - if all goes well, the new table should appear in ArcView.  If it shares a field with a theme's attribute table, then it can be joined to that table and data from Access can be displayed on the map.

Using the ODBC Manager to Configure a Connection

ODBC stands for Open Database Connectivity, and the ODBC Manager in Windows NT manages how different databases, including Excel, Access, Arcview, etc., can link to one another.  Before any link is made, a connection must be established by using the ODBC Manager.  Because others may have used and established these connections already, you generally will not have to do this yourself.  However, if you do not find an existing Access connection from ArcView, you will have to use the ODBC Manager to establish one.  The following steps tell you how to do this (note: these instructions assume that MS-Access SQL driver has been installed with Microsoft Access - this is only done by doing the CUSTOM installation of Microsoft Access or Microsoft Office; a "typical" installation does not install these drivers!).

1. Click on the Windows START button, then go to SETTINGS - CONTROL PANEL

2. In the CONTROL PANEL window, double click on the ODBC icon.

3. You will see the following interface form:
   

4. Under the User DSN tab, click Add.

5. Select the Microsoft Access Driver and click Finish.

6. For Data Source Name, type ms-access

7. Choose OK. The "ms-access" driver should now appear under User Data Sources

8. Click OK to exit

9. Close the CONTROL PANEL

Important Note! If you do this in the middle of an ArcView session, ArcView will not "see" the new connection options in the SQL Connect form - you must exit out of ArcView and open it again for the new connection to be visible.

Useful Web Sites

See also Mapping Environmental Data Stored in Microsoft Access, an exercise developed by Andrew Romanek and David Maidment for CE394K: GIS in Water Resources,  Department of Civil Engineering, The University of Texas at Austin.