Add a subset of records to ArcMap from a table

Summary

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

Procedure

1. 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.
2. Start ArcMap.
3. Add a new UIButtonControl to ArcMap's Standard toolbar: How To: Create a new UIButtonControl
4. Right-click the new tool and select View Source.
5. Locate 'Private Sub...' line.
6. 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

7. Identify the Connection String Properties for the database.
   1. Open ArcCatalog.
   2. Select Add OLE DB Connection from the Database Connections folder.
   3. Select Microsoft OLE BD Provider for ODBC Drivers from the Data Link Properties dialog box.
   4. Click Next.
   5. Select the Use Connection String radio button.
   6. Click the Build button.
   7. In the Select Data Source dialog box, click the Machine Data Source tab.
   8. Select the DSN and Click OK.
   9. Select the text in the Connect String field and press Ctrl-C on the keyboard.
8. Edit the Connection Properties in the code.
   1. Scroll down in the VBA window to the line '++CHANGE #1.
   2. Delete the text after the comma on the next line.
   3.  Press Ctrl+V to paste the connection string.
   4. Put a double quotation mark before and after the string.
9. Edit the SQL command. Each RDBMS may use slightly different SQL syntax. Consult the Help documentation to determine the correct SQL string.
   1. Scroll down in the VBA window to the line '++CHANGE #2.
   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'

SQLstr = "create view NEWVIEW as select * from STREETS where STATUS = 'CURRENT'

Note:
The view must not already exist in an Access database.

10. Specify which table from the RDBMS to add to ArcMap.
    1. Scroll to the line '++CHANGE #3.
    2. 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.
11. Switch back to ArcMap.
12. Click the New tool added in Step 3.
13. Click the Source tab in the table of contents to see the view.