How to make a query-based layer

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

Paste the code into your VBA Application.
Adjust the code to connect to a geodatabase containing USA counties and states data.
From the Macros dialog, run the MakeFeatureQueryClass macro.

[VBA]


        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