How to add OLE DB table as an XYEvent layer to an ArcMap document

This example demonstrates how to add a table from an OLE DB data source, in this case an RDBMS, to an ArcMap document as an XYEvent layer.

How to use

  1. Add a new UIButtonControl to your ArcMap document.
  2. Paste the code into the 'click' method of the button control.
  3. Modify the connection properties, table names, column names and coordinate system where appropriate.
[VBA] 
Private Sub UIButtonControl1_Click()
    '++ adds an OLE DB table as an XYEvent layer
    '++
On Error GoTo ErrorHandler:
    '++ New property set for workspacefactory
    Dim pPropset As IPropertySet
    Set pPropset = New PropertySet
    
    '++ Provider = MS OLE DB provider for Oracle.
    pPropset.SetProperty "CONNECTSTRING", "Provider=MSDAORA.1;Data source=mydatabase;User ID=oledb;Password=oledb"
    
    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
    SQLstr = "create or replace view XYTABLE_V as select * from XYTABLE where place like 'R%' "
    
    '++ 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
        If pDataset.Name = "OLEDB.XYTABLE_V" 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 pTableName As IName
    Dim pDS As IDataset
    Set pDS = pTable
    Set pTableName = pDS.FullName
    
    '++ Set the field properties for the event theme
    Dim pXYEvent2FieldsProperties As IXYEvent2FieldsProperties
    Set pXYEvent2FieldsProperties = New XYEvent2FieldsProperties
    With pXYEvent2FieldsProperties
        .XFieldName = "LONGITUDE"
        .YFieldName = "LATITUDE"
        .ZFieldName = ""
    End With
    
    '++ Set the spatial reference for the event theme
    Dim pSpatialReferenceFactory As ISpatialReferenceFactory
    Dim pProjectedCoordinateSystem As IProjectedCoordinateSystem
    
    Set pSpatialReferenceFactory = New SpatialReferenceEnvironment
    Set pProjectedCoordinateSystem = pSpatialReferenceFactory.CreateProjectedCoordinateSystem(esriSRProjCS_NAD1983UTM_11N)
    
    Dim pXYEventSourceName As IXYEventSourceName
    Set pXYEventSourceName = New XYEventSourceName
    
    '++ Combine all the properties for the event theme
    With pXYEventSourceName
        Set .EventProperties = pXYEvent2FieldsProperties
        Set .SpatialReference = pProjectedCoordinateSystem
        Set .EventTableName = pTableName
    End With
    
    Dim pName As IName
    Dim pXYEventSource As IXYEventSource
    Set pName = pXYEventSourceName
    Set pXYEventSource = pName.Open
    
    '++ Create a new feature layer object for the event theme
    Dim pflayer As IFeatureLayer
    Set pflayer = New FeatureLayer
    Set pflayer.FeatureClass = pXYEventSource
    pflayer.Name = "XYEvent"
    
    '++ Add the layer extension (required to ensure when you edit
    '++ the layer's Source properties and click the Set Data Source
    '++ button, the correct "Add XY Events" Dialog appears)
    Dim pLayerExt As ILayerExtensions
    Dim pRESPageExt As New XYDataSourcePageExtension
    Set pLayerExt = pflayer
    pLayerExt.AddExtension pRESPageExt
    
    '++ add the new layer to the display
    pMap.AddLayer pflayer
    
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & Err.Description
    
End Sub