Report that shows relationships in a table format

[Mrsbex]

Sorry if this has been asked before, I tried to search for "relationship report" but it told me it was too long, or too common to search for...

Is it possible to show the relationships in a table format to print out?
I'm looking for something like this...



Table 1 - Table 2 - Join Properties

eg.
Purchase Ledger (Account Ref) - Purchase Order (Account Ref) - Indeterminate (All from both tables.)

Is that possible?

[orange]

See this thread for ideas.

[davegri]

This works for one to many. Haven't tested for esoteric setups.

Public Sub GetRelationships()    
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    Set db = CurrentDb
    For Each rel In db.Relations
        If Left(rel.Table, 4) <> "MSys" Then
            For Each fld In rel.Fields
                Debug.Print rel.Table & " PK=" & fld.Name & " >>>>>  " & rel.ForeignTable & " FK=" & fld.ForeignName
            Next
        End If
    Next
End Sub

[Mrsbex]

Thanks Davegri,

How do I incorporate that code into the database? I'm only used to using the standard interface, not modules. Sorry for my ignorance and don't worry if it's too much to explain, I can go find it on you tube if needed. I'm not expecting a tutorial lol

[Mrsbex]

Thanks orange,

I've got this query from that link but the relationships don't give much detail...

SELECT MSysRelationships.szObject, MSysRelationships.szColumn, MSysRelationships.szReferencedObject, MSysRelationships.szReferencedColumn, MSysRelationships.szRelationship
FROM MSysRelationships;

See this thread for ideas.

[orange]

You may be able get more details of attributes of Relations by using of the RelationAtributesEnum

[davegri]

Relationships.zip
To incorporate this into your DB, copy form frmOne, module basCommon, and report rptRelationships.
All the tables in the attached db are just dummy examples to show how it looks and works.

[Mrsbex]

Thanks guys, brilliant stuff. Got what I needed, you've been a massive help!

[orange]

Do you have a "solution" that you'd like to share?

[Mrsbex]

Hmm, probably not on the level you guys are used to! I used the query:-

SELECT MSysRelationships.grbit, MSysRelationships.szObject, MSysRelationships.szColumn, MSysRelationships.szReferencedObject, MSysRelationships.szReferencedColumn, MSysRelationships.szRelationship
FROM MSysRelationships;

then using the link you suggested I looked at the attributes of the RelationAtributesEnum and worked out the types of relationships from the numbers under the grbit field. As I only had 5 in the end that I needed to know about I never bothered with a report and just printed out the query. Once my tables had been re-linked I re-established the relationships, and all is working well...

[orange]

Thanks. That may help someone else.