[AccessD] Relationships

Max,

    Your existing code seems to be OK. You might like to incorporate the ForeignName property as well. Sample function Fn_GetRelations(), based upon your code, is placed below.

    For storing the particulars in a table, sample procedure P_FillRelationsTable() as given below, could be used. This will populate table T_Relations having following fields:
    (a) RelName, (b) RelAttributes
    (c) TableName, (d) ForeignTableName
    (e) FieldName, (f) ForeignFieldName

    In case of composite key involving multiple fields, all such fields get listed by the function as well as procedure mentioned above.

Best wishes,
A.D. Tejpal
------------

' Code in VBA module
'=================================
Function Fn_GetRelations() As String
    Dim fd As DAO.Field
    Dim rel As DAO.Relation
    Dim Txt As String
    
    Txt = ""
    For Each rel In CurrentDb.Relations
       With rel
            Txt = Txt & "RelName: " & .Name & vbCrLf
            Txt = Txt & "RelAttributes: " & _
                        .Attributes & vbCrLf
            Txt = Txt & "Table: " & .Table & vbCrLf
            Txt = Txt & "ForeignTable: " & _
                        .ForeignTable & vbCrLf
            Txt = Txt & "Fields:" & vbCrLf
            For Each fd In .Fields
                Txt = Txt & "Field: " & fd.Name & _
                        ",  ForeignField: " & _
                        fd.ForeignName & vbCrLf
            Next
       End With
       Txt = Txt & vbCrLf
    Next
    
    Fn_GetRelations = Txt
    
    Set rel = Nothing
    Set fd = Nothing
End Function
'-----------------------------------------------

Sub P_FillRelationsTable()
    ' Populates table T_Relations with particulars
    ' of all relations in the db
    ' This table has fields: RelName, RelAttributes,
    ' TableName, ForeignTableName,
    ' FieldName, ForeignFieldName
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fd As DAO.Field
    Dim rel As DAO.Relation
    
    Set db = CurrentDb
    
    ' Clear table T_Relations
    db.Execute "DELETE FROM T_Relations;", _
                        dbFailOnError
    Set rst = db.OpenRecordset("T_Relations")
    For Each rel In db.Relations
        ' Populate table T_Relations with
        ' uptodate particulars
        For Each fd In rel.Fields
            With rst
                .AddNew
                
                .Fields("RelName") = rel.Name
                .Fields("RelAttributes") = rel.Attributes
                .Fields("TableName") = rel.Table
                .Fields("ForeignTableName") = _
                                            rel.ForeignTable
                .Fields("FieldName") = fd.Name
                .Fields("ForeignFieldName") = _
                                            fd.ForeignName
                
                .Update
            End With
        Next
    Next
    
    ' Attributes value can be a combination of
    ' one or more of the following:
    ' dbRelationUnique = 1
    ' dbRelationDontEnforce = 2
    ' dbRelationInherited (Linked Table) = 4
    ' dbRelationUpdateCascade = 256
    ' dbRelationDeleteCascade = 4096
    ' dbRelationLeft = 16777216
    ' dbRelationRight = 33554432
    
    ' Examples:
    ' (a) Linked tables with UpdateCascade type
    '       relationship:
    '       Attributes =  4 +256 = 260
    
    ' (b) Local tables with dbRelationLeft +
    '       UpdateCascade type relationship:
    '       Attributes =  16777216 +256 = 16777472
    
    ' (c) Local tables with dbRelationRight +
    '       UpdateCascade type relationship:
    '       Attributes =  33554432 +256 = 33554688
    
    rst.Close
    Set rst = Nothing
    Set rel = Nothing
    Set fd = Nothing
    Set db = Nothing
End Sub
'========================

  ----- Original Message ----- 
  From: Max Wanadoo 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, February 19, 2010 01:07
  Subject: Re: [AccessD] Relationships


  Hi Gustave,

  Did you have a  change  to look at the  relationship pic I sent?

  I cannot seem to get the collection to see these other joins.

  Any  help  or insights would be  appreciated.

  Thanks

  Max


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
  Sent: 18 February 2010 14:53
  To: accessd at databaseadvisors.com
  Subject: Re: [AccessD] Relationships

  Hi Max

  So you have compound indexes (or indices)? If not, only one field belongs to
  the index that is tied to the relation.

  /gustav


  >>> max.wanadoo at gmail.com 18-02-2010 15:29 >>>
  Can anybody help me with the following code.  It doesn't  seem to want to
  give me anything other that  the  first field join.

  Thanks

  Max

  For Each Rel In CurrentDb.Relations
     With Rel
     sql = sql & "Name: " & .Name & vbCrLf
     sql = sql & "Attributes: " & .Attributes & vbCrLf
     sql = sql & "Table: " & .Table & vbCrLf
     sql = sql & "ForeignTable: " & .ForeignTable & vbCrLf
     sql = sql & "Fields:" & vbCrLf
     For Each Fld In .Fields
       sql = sql & "Field: " & Fld.Name & vbCrLf
     Next Fld
     End With
     sql = sql & vbCrLf
  Next