English

How To: Add a subset of records to ArcMap from a table

Summary

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.

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.
     
    Code:
    '++ 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 your database.
     
    A. Open ArcCatalog.
    B. Select Add OLE DB Connection from the Database Connections folder.
    C. Select Microsoft OLE BD Provider for ODBC Drivers from the Data Link Properties dialog box.
    D. Click Next.
    E. Select the Use Connection String radio button.
    F. Click the Build Button.
    G. In the Select Data Source dialog box, click the Machine Data Source tab.
    H. Select the DSN and Click OK.
    I. Select the text in the Connect String field and press Ctrl-C on the keyboard.
  8. Edit the Connection Properties in the code.
    A. Scroll down in the VBA window to the line '++CHANGE #1.
    B. Delete the text after the comma on the next line.
    C. Press Ctrl+V to paste the connection string.
    D. 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.
    A. Scroll down in the VBA window to the line '++CHANGE #2.
    B. 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.

    Oracle:
    Code:
    SQLstr = "create or replace view NEWVIEW as select * from STREETS where STATUS = 'CURRENT'

    MS Access:
    Code:
    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.
    A. Scroll to the line '++CHANGE #3.
    B. 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.