How to find out tables that have no relationships? 1

[UHNSTrust]

I have a medical database with 200 plus tables (lots of code lookup tables).

During design I must have forgot to create referential integrity on one and only found out about it by chance.

How could you through code debug print all the tables that do not have any relationships set up?

Thanks in advance

Jonathan

 

[glalsop]

This will list all of the tables that are part of a relationship, but it will not produce a distinct list. Since you have so many tables, it may be worthwhile to store them in a temporary table, then select distinct names in a query. Then you could also select distinct names of all of the tables in your database, and run a query against both result sets to see which tables were left out of relationships.

Sub ShowTablesWithRels()

Dim dbs As DAO.Database
Dim rel As DAO.Relation

Set dbs = CurrentDb()

For Each rel In dbs.Relations
    Debug.Print rel.Table & vbCrLf & rel.ForeignTable
Next rel
    
End Sub

-Gary