Microsoft Access & ArcGIS

Microsoft Access & ArcGIS
Microsoft Access
The use of Access is becoming increasingly common in the field of GIS. Unlike Excel, Access is a
relational database that allows you to actively create relationships between data tables. Access can
be preferred over Arc because it is more efficient, information only needs to be stored in one place
and queries can be created to extract necessary information from the tables. It gives a GIS user
flexibility in changing, maintaining, updating, and querying data more effectively than a GIS program.
Data from Access can be added to ArcMap in two ways: 1) Static data can be added as a .dbf file; 2)
Data that is updated regularly or continually can be added using SQL and ODBC, which automatically
updates files in a GIS.
For this project, I ran queries in Access, and added them as .dbf files to ArcMap. For more
information on adding data using SQL or ODBC, visit:
When you open an Access file, you will see the following window:
Figure 1 The main window in Microsoft Access.
Data is stored in tables that are created using either the Design view, the wizard, or entering in the
data (Figures 2 & 3). Tables can then be joined together, which prevents data redundancy, and can
thus reduce the size of the database.
Figure 2 The design view of a table. Fields can be created here, and descriptions can be made for each field for future reference.
Figure 3 Data organized in a table, including spatial data.
To create a query using existing tables, use the Design view or the wizard. Tables are joined
together using arrows, and queries allow you to extract information from existing tables without
altering them (Figure 4). Queries can be edited in the lower window of the query design view (Figure
Figure 4 Query design view. Notice how three tables are connected using arrows. Running the query will extract the appropriate
information from each of the tables.
Figure 5 The bottom section of the query design view. Queries can be easily edited using the drop down menus in each cell.
Checking the box means the data will be shown after the query is run, and filling out the criteria cell will specify what unique
information should be shown from that specific field.
After a query is run by pressing the red exclamation point on the toolbar, a data table is created with
the data specified in the query (Figure 6).
Figure 6 A query output.
To export the data table, go to File Æ Export
Select the appropriated folder, name the file, and save as file type dBASE IV (*.dbf)
Open ArcMap, and on the Menu, select Tools Æ Add XY data.
Navigate to the .dbf file. Select the Fields with the appropriate spatial coordinates.
Select or Import a coordinate system. When the description window has coordinate information, hit
The data is added to the display window in ArcMap!
Notes: Occassionally, ArcMap is not able to directly import the .dbf file from Access. One way
around this is to export the table from Access as an Excel file (.xls). Open the file in Excel, and save
it as dBASE IV (*.dbf). This usually solves the problem. Also, make sure the .dbf file is dBASE IV,
otherwise ArcMap will not import it!