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:
Free weekly newsletter
Fill out your e-mail address to receive our newsletter!
DDE
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.
ODBC
Acronym for Open DataBase Connectivity. Databases that use ODBC are configured through the ODBC Data Sources found in the Control Panel directory.
SQLCon
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.
theDB=”c:\avtemp\surveys.mdb”
‘This lines is the pathway to your MS Access Database
theSQL.Login(“DBQ=”+theDB)
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)
myTable=Table.Make(theVtab)
av.GetProject.AddDoc(mytable)
mytable.GetWin.Open
theView=av.FindDoc(“view1”)
‘Now you want to go to your view containing the shapefile you’d like to join.
theTheme=theView.FindTheme(“Culverts.shp”)
‘Now you want to select the theme to join
msgbox.info(theTheme.AsString,””)
‘This is an optional message box to let you know which theme you’ve picked
theTab=theTheme.GetFTab
‘Now you want to open the attribute table of the theme
theField1=theTab.FindField(“rcd_code”)
‘This line selects the field from the shapefile which will be the join field
theTabFromAccess=VTab.MakeSQL(theSQL,astring)
theFieldToJoin=theTabFromAccess.FindField(“Infra_id”)
‘Now you will select the field of the MS Access table to join to the coverage
theTab.Join(theField1,theTabFromAccess,theFieldToJoin)
‘This line joins the two tables based on the link fields you designated in the few lines of code above
theSQL.Logout
‘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)
end
‘This loop runs through each selection to pull the values from the field Link99. ActID is the variable that contains this value.
msgbox.info(ActID, ” “)
‘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 “)
end
theRequest.execute(Condition)
‘Execute the requests in the statements above based on the input parameters. theRequest.Close
‘Close the request
‘End of script
