Persisting Non_ObjectID table selections

Description:

This example demonstrates how to create and persist a selection set using a table without an ObjectID field table.

How to use:
  1. Paste the code into your ArcMap/VBA application.
  2. Add two new UIButtonControls to your ArcMap document.
  3. Add the form, userform1.

Option Explicit
'++ As the non-OID table doesn't have an ObjectID field,
'++ this selection would normally cease to exist when
'++ the tablewindow is closed. The selection can be
'++ persisted to the local scratchworkspace
'++ as a new temporary table
'++
'++ NB: This is static copy of the selection set
'++ - any changes made to the base table will
'++ invalidate the selection

'++ If the temp table with the selection set is saved with the
'++ document, when the document is closed and re-opened,
'++ it will no longer be accessible as the
'++ document will be using a new scratchworkspace on startup.

Private pTableCollection As esriCore.ITableCollection
Private pMxDoc As esriCore.IMxDocument
Private pStandaloneTable As esriCore.IStandaloneTable
Private sQuery As String

Private Sub UIButtonControl1_Click()
'++ Add an OLE DB (Non-OID) table
On Error GoTo EH

  Dim connStr As String
  '++ This sample uses the MS OLE DB provider for Oracle
  connStr = "Provider=MSDAORA.1;Data source=olesde;User ID=oledb;Password=oledb"
  
  Dim pPropSet As esriCore.IPropertySet
  Set pPropSet = New esriCore.PropertySet
  pPropSet.SetProperty "CONNECTSTRING", connStr
  
  '++ connect to database
  Dim pWkFac As esriCore.IWorkspaceFactory
  Set pWkFac = New esriCore.OLEDBWorkspaceFactory
  Dim pFeatureWorkspace As esriCore.IFeatureWorkspace
  Set pFeatureWorkspace = pWkFac.Open(pPropSet, 0)
  If pFeatureWorkspace Is Nothing Then Exit Sub
  
  Dim pTableSrc As esriCore.ITable
  Set pTableSrc = pFeatureWorkspace.OpenTable("codemog")
  
  '++ add to the map 
  Set pMxDoc = ThisDocument
  Set pTableCollection = pMxDoc.ActiveView.FocusMap
  pTableCollection.AddTable pTableSrc
  
  '++ Update the Map document contents
  pMxDoc.UpdateContents
 
Exit Sub

EH:
 MsgBox Err.Description, vbInformation, "UIButtonControl1_Click"

End Sub

Private Sub UIButtonControl2_Click()
'++ Persisting the selected standalone table item
'++ in the local scratchworkspace as a new table.

On Error GoTo EH

Dim pSelItem As IUnknown
Dim pApp As IApplication
Set pApp = Application
   
Set pMxDoc = ThisDocument
Set pSelItem = pMxDoc.SelectedItem

 If pSelItem Is Nothing Then
  MsgBox "Nothing selected", vbExclamation
  Exit Sub
 '++ If a table is selected
 ElseIf Not TypeOf pSelItem Is IStandaloneTable Then
  MsgBox "No table selected", vbExclamation
  Exit Sub
 Else
  Set pStandaloneTable = New esriCore.StandaloneTable
  Set pStandaloneTable = pSelItem
   If pStandaloneTable.Table.HasOID Then
    Exit Sub
   End If
 End If

  Dim pTableSrc As esriCore.ITable, pTableSel As esriCore.ITable
  Dim pSelSet As esriCore.ISelectionSet
  Dim pWkspFact As esriCore.IScratchWorkspaceFactory2
  Dim pQuery As esriCore.IQueryFilter
  Dim pTableWindow As esriCore.ITableWindow2, pTablewindow2 As esriCore.ITableWindow2
  Dim pTableView As esriCore.ITableView2
  Dim pWksp As esriCore.IWorkspace
  Dim pFields As esriCore.IFields, pFields2 As esriCore.IFields
  Dim pTableCtrlInfo As esriCore.ITableControlInfo
  Dim pTableViewAll As esriCore.ITableControl2
  Dim pCursor As esriCore.ICursor, pCursor2 As esriCore.ICursor
  Dim pStTab2 As esriCore.IStandaloneTable
  Dim sTabNm As String
  Dim pDS As esriCore.IDataset
  Dim pED As esriCore.IEnumDataset
  Dim j As Integer, i As Integer
  Dim pRow As IRow
  Dim pRowBuf As IRowBuffer
  
  '++ Create a TableWindow object, configure and open it.
  Set pStandaloneTable = New esriCore.StandaloneTable
  Set pStandaloneTable = pSelItem
  Set pTableWindow = New esriCore.TableWindow
  Set pTableSrc = pStandaloneTable.Table
  Set pFields = pTableSrc.Fields

  Set pTableWindow.StandaloneTable = pStandaloneTable
  pTableWindow.TableSelectionAction = esriSelectFeatures
  pTableWindow.ShowSelected = False
  Set pTableWindow.Application = pApp
  If Not pTableWindow.IsVisible Then pTableWindow.Show True
 
  '++ hook into the temoprary table (an OID table) that supports the table window
  Set pTableView = pTableWindow.TableControl
  Set pTableCtrlInfo = pTableView
  Set pTableViewAll = pTableView
  
  '++ Create a new query object
  Set pQuery = New esriCore.QueryFilter
  pQuery.WhereClause = ""
  
  '++ Open a search cursor on the source table to count rows
  Set pCursor = pStandaloneTable.Table.Search(pQuery, True)
  If CountRows(pCursor) <= 1 Then
   MsgBox "Insufficient rows to perform selection", vbInformation
   Exit Sub
  End If
  Set pCursor = Nothing
  
  '++ Load the whole table into the table window
  '++ -required for selections to work on Non-OID tables
  pTableViewAll.ReadToEndOfTable
  
  '++ Get the table name - trap for text files or table names with '.'
  sTabNm = pStandaloneTable.Name
 
  Dim FindDot As String, Pos As Long, StrLen As Integer
  StrLen = Len(sTabNm)
  Pos = 1
  Do Until Pos = StrLen
   FindDot = Mid(sTabNm, Pos, 1)
    If FindDot = "." Then
     Mid(sTabNm, Pos, 1) = "_"
    End If
   Pos = Pos + 1
  Loop
 
  '++ Fetch the table fields collection
  Set pFields2 = pStandaloneTable.Table.Fields
  
  '++ Call the 'select by attr for non-oid tables' form
  Load_sel_form pFields2
  pQuery.WhereClause = sQuery
  
  If pQuery.WhereClause = "" Then  'cancelled
   Exit Sub
  End If
  
  '++ Set up the current scratchworkspace
  Set pWkspFact = New esriCore.ScratchWorkspaceFactory
  Set pWksp = pWkspFact.CurrentScratchWorkspace
  Set pSelSet = pTableView.Table.Select(pQuery, esriSelectionTypeHybrid, esriSelectionOptionNormal, pWksp)
    
  '++ Check to see if any rows returned
  If pSelSet.Count = 0 Then
   MsgBox "Selection returned 0 rows"
   Exit Sub
  End If
  
  '++ Apply selection to the table
  Set pTableView.SelectionSet = pSelSet
  
  '++ Update the selection on the table
  pTableViewAll.UpdateSelection pSelSet
  
  '++ Test to see if temp table already exists: if yes, delete it.
  Set pED = pWksp.Datasets(esriDTTable)
  Set pDS = pED.Next
   Do Until pDS Is Nothing
     If pDS.Name = sTabNm Then
      pDS.Delete
      Exit Do
     End If
     Set pDS = pED.Next
   Loop
   
  Dim pUID As IUID
  Set pUID = New UID
  pUID.Value = "esriCore.Object"
  Dim pTmpFeatWksp As esriCore.IFeatureWorkspace
  Set pTmpFeatWksp = pWksp
  
  '++ Create the new temp table
  Set pTableSel = pTmpFeatWksp.CreateTable(sTabNm, pFields2, pUID, Nothing, "")
  
  '++ Create a new tablewindow object
  Set pTablewindow2 = New esriCore.TableWindow
  
  '++ Create a new stand alone table object
  Set pStTab2 = New StandaloneTable
  Set pStTab2.Table = pTableSel
  
  '++ Re-open the search cursor to read values from
  Set pCursor = pStandaloneTable.Table.Search(pQuery, True)
  
  '++ Open an insert cursor on the new table
  Set pCursor2 = pTableSel.Insert(True)
  
  '++ Create a rowbuffer for row inserts
  Set pRowBuf = pTableSel.CreateRowBuffer
     
  '++ Loop through the query cursor, read values and write to new table
  For j = 0 To pSelSet.Count - 1  '++ for every row in selected set
    Set pRow = pCursor.NextRow
      If Not pRow Is Nothing Then
       i = 0
       Do Until i = pFields2.FieldCount '++ for every field in the row
         If Not IsEmpty(pRow.Value(i)) Then
           pRowBuf.Value(i) = pRow.Value(i)
         End If
        i = i + 1
       Loop
       '++ Insert the row
       pCursor2.InsertRow pRowBuf
      End If
  Next j
 
  '++ Add the new table to the table collection
  Set pTableCollection = pMxDoc.ActiveView.FocusMap
  pTableCollection.AddTable pTableSel
  
  '++ Assoc. new table with new table window
  Set pTablewindow2.StandaloneTable = pStTab2
  pTablewindow2.TableSelectionAction = esriSelectFeatures
  pTablewindow2.ShowSelected = False
  Set pTablewindow2.Application = pApp
  
  If Not pTablewindow2.IsVisible Then pTablewindow2.Show True
  pMxDoc.UpdateContents
 
  '++ Re-reference objects
  Set pRowBuf = Nothing
  Set pRow = Nothing
  Set pSelSet = Nothing
  Set pCursor = Nothing
  Set pCursor2 = Nothing

 
Exit Sub
EH:
MsgBox Err.Description, vbInformation, "UIButtonControl2_Click"

End Sub

Public Sub Load_sel_form(pFlds As esriCore.IFields)
 '++ Sel by attribute form load
 On Error GoTo EH
 Dim f As IField
 Dim i As Integer
  For i = 0 To (pFlds.FieldCount - 1)
    Set f = pFlds.Field(i)
    UserForm1.ListBox1.AddItem f.Name
  Next i
   
 UserForm1.Label1.Caption = "fields"
 UserForm1.Label2.Caption = "values"
 UserForm1.Label3.Caption = "select * from "
 UserForm1.Label4.Caption = pStandaloneTable.Name & "  where"
 UserForm1.Show vbModal

Exit Sub
EH:
MsgBox Err.Description, vbInformation, "Load_sel_form"

End Sub

Public Sub Load_unq_values(pStr As String)
'++Get the unique values for the selected field
On Error GoTo EH
 Dim i As Integer
 Dim strChk As String
 Dim pR As IRow
 Dim pF As IField
 Dim pFS As IFields
 Dim pC As ICursor
 Dim pQ As IQueryFilter
 Set pQ = New QueryFilter
 pQ.WhereClause = ""
 Set pFS = pStandaloneTable.Table.Fields
 
 '++ Open a cursor for whole table
 Set pC = pStandaloneTable.Table.Search(pQ, False)
 i = pFS.FindField(pStr)
 Set pF = pFS.Field(i)
 
 Set pR = pC.NextRow
 '++ Add only unique values
 Do Until pR Is Nothing
  If Not IsNull(pR.Value(i)) Then
   If pR.Value(i) <> strChk Then
    If pF.Type = esriFieldTypeString Then
     UserForm1.ListBox2.AddItem "'" & pR.Value(i) & "'"
    Else
     UserForm1.ListBox2.AddItem pR.Value(i)
    End If
   End If
   strChk = pR.Value(i)
  End If
  Set pR = pC.NextRow
 Loop
 
 Set pC = Nothing
 Set pR = Nothing
 Set pF = Nothing
 Set pFS = Nothing
 Set pQ = Nothing
 
 Exit Sub
EH:
 MsgBox Err.Description, vbInformation, "Load_unq_values"
End Sub

Public Sub Get_query(strQ As String)
'++ Accepts the SQL string from userform1
On error goto EH
 sQuery = strQ
Exit sub
EH:
 MsgBox Err.Description, vbInformation, "Get_query"
End Sub

Private Function CountRows(pCur As esriCore.ICursor)
'++ Count number of rows in cursor
on error goto EH
Dim i As Integer
Do Until pCur.NextRow Is Nothing
 pCur.NextRow
 i = i + 1
Loop
CountRows = i
Exit function
EH:
 MsgBox Err.Description, vbInformation, "CountRows"
End Function

Application: ArcMap

Difficulty: Intermediate


Visual Basic
File Description
userform1.frm Select By Attribute Dialog for nonOID tables


Key CoClasses: OleDBWorkspaceFactory, StandAloneTable, TableWindow, QueryFilter, ScratchWorkspaceFactory
Key Interfaces: IWorkspaceFactory, IFeatureWorkspace, ITable, ISelectionSet, IScratchWorkspaceFactory2, IQueryFilter, ITableWindow2, IField, IFields, ITableControlInfo, ITableControl2, IStandAloneTable, IDataSet, IRowBuffer, ITableControl2, IStandAloneTable