Make query-based layer

Created:7/21/2000
Description:

This example shows how to add a layer to ArcMap that is defined by a attribute query. The query could be a join between multiple feature classes or tables. The example is an attribute join between USA counties and states, enabling the states attributes to be present in the resulting counties feature layer. No data is created in this example - the layer is defined by a query on existing data. Note that this functionality is only available for geodatabases.

How to use:
  1. Paste the code into your VBA Application.
  2. Adjust the code to connect to a geodatabase containing USA counties and states data.
  3. From the Macros dialog, run the MakeFeatureQueryClass macro.
Public Sub MakeFeatureQueryClass()
  On Error GoTo ErrorHandler

  ' Connect to the database
  Dim pFeatureWorkspace As IFeatureWorkspace
  Dim pPropSet As IPropertySet
  Dim pSdeFact As IWorkspaceFactory
  
  Set pPropSet = New PropertySet
  With pPropSet
    .SetProperty "SERVER", "cuillin"
    .SetProperty "INSTANCE", "esri_sde"
    .SetProperty "DATABASE", ""
    .SetProperty "USER", "jim"
    .SetProperty "PASSWORD", "jim"
    .SetProperty "VERSION", "SDE.DEFAULT"
  End With
  
  Set pSdeFact = New SdeWorkspaceFactory
  ' Inline QI to IFeatureWorkspace
  Set pFeatureWorkspace = pSdeFact.Open(pPropSet, 0)
  
  
  ' Define the query
  Dim pQueryDef As IQueryDef
  Set pQueryDef = pFeatureWorkspace.CreateQueryDef
  With pQueryDef
    .Tables = "COUNTIES, STATES"
    .SubFields = "COUNTIES.SHAPE, COUNTIES.OBJECTID, COUNTIES.NAME, STATES.STATE_ABBR"
    .WhereClause = "COUNTIES.STATE_FIPS = STATES.STATE_FIPS"
  End With
  
  ' Get the feature class
  Dim pFeatureClass As IFeatureClass
  Dim pFeatureClassContainer As IFeatureClassContainer
  Set pFeatureClassContainer = pFeatureWorkspace.OpenFeatureQuery("My counties join", pQueryDef)
  If (pFeatureClassContainer.ClassCount <> 1) Then
    MsgBox "Failed to create feature class by query"
  Else
    Set pFeatureClass = pFeatureClassContainer.Class(0)
  End If
  
  ' Add feature class as layer to the map
  Dim pFeatureLayer As IFeatureLayer
  Set pFeatureLayer = New FeatureLayer
  Set pFeatureLayer.FeatureClass = pFeatureClass
  pFeatureLayer.Name = pFeatureClass.AliasName
  
  Dim pMxDoc As IMxDocument
  Set pMxDoc = ThisDocument
  Dim pMap As IMap
  Set pMap = pMxDoc.FocusMap
  pMap.AddLayer pFeatureLayer

  Exit Sub
ErrorHandler:
    MsgBox Err.Description, vbInformation, "MakeFeatureQueryClass"
End Sub