How To: Add a subset of records to ArcMap from a table
Note: This article pertains to ArcGIS versions 8.x and 9.x. Later versions of ArcGIS may contain different functionality, as well as different names and locations for menus, commands and geoprocessing tools.
With versions earlier than ArcGIS 8.2, you can not define a subset of records for a table in ArcMap. You can specify a Definition query for a Layer so that the layer is comprised of only the records that match the query however, this functionality is not available for tables in ArcMap.
If the tabular data is stored in an RDBMS, which supports table views and can receive SQL statements, you can add a subset of records to ArcMap. The VBA code in this article connects to the database using OLE DB, then sends a SQL command to the database that creates a view comprised of a subset of records. The view is then added to ArcMap as a table.
Note: There are other methods to accomplish this task using later versions of ArcGIS
- With ArcGIS version 8.2, the ability to add a table to ArcMap and then specify a definition query was added.
- With ArcGIS version 8.3, functionality was added to directly create a table in ArcMap based on a query.
For more information, look for a section labeled Tables and layers based on a query in the what's new in ArcGIS 8.3 help topic.
Note: Support for Visual Basic for Applications (VBA) for ArcMap and ArcCatalog ended with the ArcGIS 10.2.2 release, and Esri has not included VBA compatibility setups since version 10.5. See: ArcGIS Desktop and VBA Moving Forward
- Create a Data Source Name (DSN) for the database containing the table. This process is slightly different for each database and each version of Windows. Consult the Windows Help and your RDBMS instructions.
- Start ArcMap.
- Add a new UIButtonControl to ArcMap's Standard toolbar: How To: Create a new UIButtonControl
- Right-click the new tool and select View Source.
- Locate 'Private Sub...' line.
- Copy the following code between the 'Private Sub...' and 'End Sub' lines.
'++ adds an OLE DB table '++ On Error GoTo ErrorHandler: '++ New property set for workspacefactory Dim pPropset As IPropertySet Set pPropset = New PropertySet '++ Provider = MS OLE DB provider for Oracle. '++CHANGE #1 pPropset.SetProperty "CONNECTSTRING", "DSN=OraODBC;UID=UName;PWD=UName;SERVER=Oracle.Test" Dim pWorkspaceFact As IWorkspaceFactory Set pWorkspaceFact = New OLEDBWorkspaceFactory '++ Create the new workspace/feature workspace objects Dim pWorkspace As IWorkspace Set pWorkspace = pWorkspaceFact.Open(pPropset, 0) Dim pFeatWorkspace As IFeatureWorkspace Set pFeatWorkspace = pWorkspace '++ If a sub-set of the table is required and views are supported by the data source '++ create a view of the table selection you wish to use. Dim SQLstr As String '++CHANGE #2 SQLstr = "create view NEWVIEW as select * from STREETS where STATUS = 'CURRENT' " '++ Create the view and fetch the datasetnames from the workspace pWorkspace.ExecuteSQL SQLstr Dim pEnumDataset As IEnumDatasetName Dim pDataset As IDatasetName Set pEnumDataset = pWorkspace.DatasetNames(esriDTAny) Set pDataset = pEnumDataset.Next Do Until pDataset Is Nothing '++CHANGE #3 If pDataset.Name = "UName.NEWVIEW" Then Exit Do End If Set pDataset = pEnumDataset.Next Loop '++ Create the new table object from the dataset name Dim pTable As ITable Set pTable = pFeatWorkspace.OpenTable(pDataset.Name) Dim pDoc As IMxDocument Set pDoc = ThisDocument Dim pMap As IMap Set pMap = pDoc.FocusMap Dim pTabCol As ITableCollection Set pTabCol = pMap pTabCol.AddTable pTable Dim pTableName As IName Dim pDS As IDataset Set pDS = pTable Set pTableName = pDS.FullName Exit Sub ErrorHandler: MsgBox Err.Number & Err.Description
- Identify the Connection String Properties for the database.
- Open ArcCatalog.
- Select Add OLE DB Connection from the Database Connections folder.
- Select Microsoft OLE BD Provider for ODBC Drivers from the Data Link Properties dialog box.
- Click Next.
- Select the Use Connection String radio button.
- Click the Build button.
- In the Select Data Source dialog box, click the Machine Data Source tab.
- Select the DSN and Click OK.
- Select the text in the Connect String field and press Ctrl-C on the keyboard.
- Edit the Connection Properties in the code.
- Scroll down in the VBA window to the line '++CHANGE #1.
- Delete the text after the comma on the next line.
- Press Ctrl+V to paste the connection string.
- Put a double quotation mark before and after the string.
- Edit the SQL command. Each RDBMS may use slightly different SQL syntax. Consult the Help documentation to determine the correct SQL string.
- Scroll down in the VBA window to the line '++CHANGE #2.
- Edit the SQL string on the next line.
See the example below, in which a new view is created named NEWVIEW, containing only the records from the table STREETS that have the value CURRENT in the STATUS field.
SQLstr = "create or replace view NEWVIEW as select * from STREETS where STATUS = 'CURRENT'MS Access:
SQLstr = "create view NEWVIEW as select * from STREETS where STATUS = 'CURRENT'
Note: The view must not already exist in an Access database.
- Specify which table from the RDBMS to add to ArcMap.
- Scroll to the line '++CHANGE #3.
- Specify the view defined by the SQL statement. Some databases require that the owner of the table be specified, followed by a period in front of the table name. See the code in Step 5 as an example.
- Switch back to ArcMap.
- Click the New tool added in Step 3.
- Click the Source tab in the table of contents to see the view.