Linking MS Access With ArcView 3.x Using Avenue

Caitlin Dempsey


Avenue is the object-oriented program native to the 3x versions of ArcView. It is a fairly simple programming language to learn. Immense functionality can be added to ArcView through the use of Avenue.

There are many online resources that offer free code and guidelines on using Avenue with ArcView. A collection of code used to achieve a function in ArcView is called a script. Scripts are recognizable by their *.ave extension. Scripts are utilized in ArcView by loading them into the Script module from the *.ave files. You can also look at Avenue scripts in a text editor.

The two sections of code in the article focus on using Avenue to interact with MS Access to extract and use data with ArcView. The first script uses ODBC to pull a table from MS Access into ArcView which is then joined to a shapefile using a common link value. The second script used a selected value from a shapefile to open up the related record in a form from MS Access. These scripts can be copied and pasted in ArcView and modified to match specific data.

Here are a few definitions to help make reading and understanding acronyms in the code easier:


Stands for Dynamic Data Exchange. DDE is a device from Microsoft that allows two applications to exchange data. ArcView is an application that supports DDE and is used to communicated with other applications such as Access, Excel and Visual Basic. The drawback of using DDE is that all applications must be located on the same machine, i.e. DDE will not run over a network.


Acronym for Open DataBase Connectivity. Databases that use ODBC are configured through the ODBC Data Sources found in the Control Panel directory.


This is the command to connect to a SQL database such as the MS Access database in these examples. ArcView uses ODBC in the Microsoft Operating Systems to achieve this communication.

How to use Avenue to join tables in Access to Shapefiles in ArcView:

This script will load a table from a MS Access database into an ArcView project and join it to a shapefile using join fields. To use this script copy the text from “Start of Script (including quote marks) until “End of Script” and paste into a blank script page. Quote (‘) marks are considered comment lines in Avenue and therefore are not read during script execution.

‘Start of Script

theSQL=SQLCon.Find(“Ms Access 97 Database”)
‘This line designates the type of ODBC connection. To see a list of the connection types you have loaded
‘go to Start à Control Panel à ODBC Data Sources. The first tab called User DSN lists the types.
‘This lines is the pathway to your MS Access Database
astring=”Select * from surv_F98″

‘SQL query to select the table within MS Access that you are interested in joining.
theVTab=VTab.MakeSQL(theSQL, astring)

‘Now you want to go to your view containing the shapefile you’d like to join.
‘Now you want to select the theme to join,””)

‘This is an optional message box to let you know which theme you’ve picked

‘Now you want to open the attribute table of the theme

‘This line selects the field from the shapefile which will be the join field
‘Now you will select the field of the MS Access table to join to the coverage

‘This line joins the two tables based on the link fields you designated in the few lines of code above
‘This closes the MS Access connection

Using Avenue to launch MS Access and Open up a Form:


This script will open up a form in MS Access to the data previously selected selected in ArcView. To use this script copy the text from “Start of Script (including quote marks) until “End of Script” and paste into a blank script page. Quote (‘) marks are considered comment lines in Avenue and therefore are not read during script execution.

‘Start of script

theView = av.GetActiveDoc
theTheme = theView.FindTheme( “Culverts.shp”)
‘These two lines find the view and shapefile to be used theFtab = theTheme.GetFtab
theThemeBitmap = theFtab.GetSelection
‘Now the attribute table for the selected features are accessedfor each r in theThemeBitmap
ActID = theFtab.ReturnValueString(theFtab.FindField( “link99”),r)
‘This loop runs through each selection to pull the values from the field Link99. ActID is the variable that contains this value., ” “)
‘ActID = SELF.asstring
Condition = “[OpenForm Survey,,,[Infra_ID] = “”” +ActID+ “””]”
‘This code will look for and open up the form ‘Survey’ and look for a field called ‘Infra_id’ which is the join field for the corresponding field ‘link99’ from the shapefile ‘Culvert.shp’

theRequest = DDEClient.Make( “MSAccess “, “System “)
if (theRequest.HasError) then
system.execute(“C:\Program Files\Microsoft Office\Office\msaccess.exe C:\temp\culv99.mdb”)
theRequest = DDEClient.Make( “MSAccess “, “System “)
‘Execute the requests in the statements above based on the input parameters. theRequest.Close
‘Close the request
‘End of script


Photo of author
About the author
Caitlin Dempsey
Caitlin Dempsey is the editor of Geography Realm and holds a master's degree in Geography from UCLA as well as a Master of Library and Information Science (MLIS) from SJSU.

Free weekly newsletter

Fill out your e-mail address to receive our newsletter!

2 thoughts on “Linking MS Access With ArcView 3.x Using Avenue”

  1. is it possible to use menu interfaces developed from Visual basic to interlink them with ArcView map analysis outputs

  2. i know its possible but i am not an expert yet, how do we use menu interfaces from VB to manipulate data from ArcView

Comments are closed.