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
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
'++ 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
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.