Connection properties of destination table

[WillBurt]

I made a script (9.3.1) that runs through the layers in a map document and changes the connection properties for that layer.  The issue I have is with layers that contain joins.  I can access the source table with no problem but have difficulty figuring out how to access the connection props of the destination table.  I Can access the IRelationship and see the table names and join fields but not the connection properties.  snips of code in vb6,.net,vba,C#, java, or just point me to a method that will work.

Thanks for your help
~Will

[NeilClemmons]

The following code will get connection info for relates and joins.  It was written specifically for SDE data so you'll want to remove the check for remote databases.  The first snippet loops through Relates and the second loops through Joins.

            ' Get connections for related tables.
            Dim relClassCollection As IRelationshipClassCollection = CType(featureLayer, IRelationshipClassCollection)
            Dim enumRelClass As IEnumRelationshipClass = relClassCollection.RelationshipClasses
            Dim relClass As IRelationshipClass = enumRelClass.Next
            Do While relClass IsNot Nothing
                Dim dataset As IDataset = CType(relClass.DestinationClass, IDataset)
                Dim workspace As IWorkspace = dataset.Workspace
                If workspace.Type = esriWorkspaceType.esriRemoteDatabaseWorkspace Then
                    If TypeOf dataset.FullName Is IDatasetName Then
                        Dim dataLayer As IDataLayer = CType(featureLayer, IDataLayer)
                        Dim datasetName As IDatasetName = CType(dataLayer.DataSourceName, IDatasetName)
                        Dim workspaceName As IWorkspaceName = datasetName.WorkspaceName

                        Dim connectionInfo As ConnectionInfo = New ConnectionInfo(workspaceName.ConnectionProperties)
                        If Not m_connections.Contains(connectionInfo) Then m_connections.Add(connectionInfo)
                    End If
                End If

                relClass = enumRelClass.Next
            Loop

            ' Get connections for joined tables.
            Dim displayTable As IDisplayTable = CType(featureLayer, IDisplayTable)
            Dim table As ITable = displayTable.DisplayTable
            Do While TypeOf table Is IRelQueryTable
                Dim relQueryTable As IRelQueryTable = CType(table, IRelQueryTable)

                Dim destinationTable As ITable = relQueryTable.DestinationTable
                Dim dataset As IDataset = CType(destinationTable, IDataset)
                Dim workspace As IWorkspace = dataset.Workspace
                If workspace.Type = esriWorkspaceType.esriRemoteDatabaseWorkspace Then
                    If TypeOf dataset.FullName Is IDatasetName Then
                        Dim dataLayer As IDataLayer = CType(featureLayer, IDataLayer)
                        Dim datasetName As IDatasetName = CType(dataLayer.DataSourceName, IDatasetName)
                        Dim workspaceName As IWorkspaceName = datasetName.WorkspaceName

                        Dim connectionInfo As ConnectionInfo = New ConnectionInfo(workspaceName.ConnectionProperties)
                        If Not m_connections.Contains(connectionInfo) Then m_connections.Add(connectionInfo)
                    End If
                End If

                table = relQueryTable.SourceTable
            Loop

[WillBurt]

Thanks Niel.  I am trying to remove embedded USER/PASSWORD SDE connection properties from layers within mxds.  Your code works to access the destination table but when I change the connection properties. It seems to drop the relationship class from the layer. Likely doing something inappropriate.  Any suggestions.

Option Explicit
Public Sub getConnectionProps()
    'for demo purposes.... will not work with groups.
    Dim pMxDoc As IMxDocument
    Dim pMap As IMap
    Dim intMap As Integer
    Dim intLayer As Integer
    Dim pLayer As ILayer
    Dim pFeatureLayer As IFeatureLayer
    Dim pDataset As IDataset
        
    Set pMxDoc = ThisDocument

    For intMap = 0 To pMxDoc.Maps.Count - 1
        Set pMap = pMxDoc.Maps.Item(intMap)
        Debug.Print pMap.Name
        For intLayer = 0 To pMap.LayerCount - 1
            Set pFeatureLayer = pMap.Layer(intLayer)
            getJoinRelate pFeatureLayer
            
        Next intLayer
        
               
    Next intMap
      
End Sub

Public Function getJoinRelate(FeatureLayer As IFeatureLayer)
    ' Get connections for related tables.
    Dim relClassCollection As IRelationshipClassCollection
    Set relClassCollection = FeatureLayer
    Dim enumRelClass As IEnumRelationshipClass
    Set enumRelClass = relClassCollection.RelationshipClasses
    Dim relClass As IRelationshipClass
    Set relClass = enumRelClass.Next
    Dim dataset As IDataset
    Dim workspace As IWorkspace
    Dim dataLayer As IDataLayer
    Dim datasetName As IDatasetName
    Dim workspaceName As IWorkspaceName
    Dim displayTable As IDisplayTable
    Dim table As ITable
    Dim relQueryTable As IRelQueryTable
    Dim destinationTable As ITable
    Do While Not (relClass Is Nothing)
            Set dataset = relClass.DestinationClass
            Set workspace = dataset.workspace
            If workspace.Type = esriWorkspaceType.esriRemoteDatabaseWorkspace Then
                If TypeOf dataset.FullName Is IDatasetName Then
                    Set dataLayer = FeatureLayer
                    changeProps dataLayer
                End If
            End If
            Set relClass = enumRelClass.Next
            Loop

            ' Get connections for joined tables.
            Set displayTable = FeatureLayer
            Set table = displayTable.displayTable
            Do While TypeOf table Is IRelQueryTable
                Set relQueryTable = table
                Set destinationTable = relQueryTable.destinationTable
                Set dataset = destinationTable
                Set workspace = dataset.workspace
                If workspace.Type = esriWorkspaceType.esriRemoteDatabaseWorkspace Then
                    If TypeOf dataset.FullName Is IDatasetName Then
                        Set dataLayer = FeatureLayer
                        changeProps dataLayer
                    End If
                End If
                Set table = relQueryTable.SourceTable
            Loop
End Function
Public Function changeProps(pDataLayer As IDataLayer2)
    Dim pProps As IPropertySet
    Dim pNames As Variant
    Dim pVals As Variant
    Dim i As Integer
    Dim datasetName As IDatasetName
    Dim workspaceName As IWorkspaceName
    Set datasetName = pDataLayer.DataSourceName
    Set workspaceName = datasetName.workspaceName
    Set pProps = workspaceName.ConnectionProperties
    pProps.GetAllProperties pNames, pVals

    For i = 0 To pProps.Count - 1
        If UCase(pNames(i)) = "USER" Then
            pDataLayer.Disconnect
            pProps.SetProperty "USER", ""
            pProps.SetProperty "PASSWORD", ""
            datasetName.workspaceName.ConnectionProperties = pProps
        End If
    Next i
End Function