List tables that have no relationships?

[securitywyrm]

Is there a way to generate a list of tables in Access that do not have any relationships in the relationships manager? The data I'm working with is a colossal spiderweb and i want to be sure none of them slipped through the cracks.

Or as another option, is there a way to export the table relationships? I could compare that against a list of tables to see if I missed any.

[Homegrownandy]

If you don't notice they are not linked then do they need to be linked? put some test data in and run through some"normal use" see if you encounter any problems.

[securitywyrm]

If you don't notice they are not linked then do they need to be linked? put some test data in and run through some"normal use" see if you encounter any problems.

All the tables I'm importing need to be linked, but unfortunately I can't rely on my eyes to ensure this (visually impaired). Plus the ability to export or view which tables do and don't have relationships in Access will be very useful in upcoming projects.

[orange]

Here is some code that will identify the relations and the tables involved in the current database. It does not show system tables or relations. It should give you a good starting point.

' ----------------------------------------------------------------
' Procedure Name: RelationX
' Purpose: Routine to print relations in currentdb
' that do not involve system tables.
' List relation name from Access, and
' the table and foreigntable names and
' the fields in table and foreigntable
' to the immediate window.
'
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 10-Mar-20
' ----------------------------------------------------------------
Sub RelationX() 'show relations for non system tables in currentdb
10        On Error GoTo RelationX_Error
          Dim dbs As DAO.Database
          Dim rel As DAO.relation
          Dim prpLoop As Property
          Dim fldLoop As Field
          Dim RCount As Long   'count each relation
20        Set dbs = CurrentDb
30        RCount = 1
          ' Print a report showing all the different parts of
          ' the relation and where each part is stored.
40        For Each rel In dbs.Relations
50        If Not rel.name Like "Msys*" Then   'only process non system tables/relationships
60            Debug.Print RCount & "  Properties of <" & rel.name & "> Relation"
70            Debug.Print "   Table = " & rel.table
80            Debug.Print "   ForeignTable = " & rel.ForeignTable
90            Debug.Print "    Fields of " & rel.name & " Relation"
100           For Each fldLoop In rel.Fields
                  'Debug.Print " " & fldLoop.name
110               Debug.Print "     Name = " & fldLoop.name
120               Debug.Print "     ForeignName = " & fldLoop.ForeignName
130           Next fldLoop
140           Debug.Print
150           End If
160           RCount = RCount + 1
170       Next rel
          
          
180       On Error GoTo 0
RelationX_Exit:
190       Exit Sub

RelationX_Error:
200       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RelationX, line " & Erl & "."
210       GoTo RelationX_Exit
End Sub

Here is a sample of the output:

1  Properties of <AnimalAnimalCapture> Relation
   Table = Animal
   ForeignTable = AnimalCapture
    Fields of AnimalAnimalCapture Relation
     Name = AnimalId
     ForeignName = AnimalId

2  Properties of <AnimalAnimalLocs> Relation
   Table = Animal
   ForeignTable = AnimalLocs
    Fields of AnimalAnimalLocs Relation
     Name = AnimalId
     ForeignName = AnimalId

Good luck with your project.

[isladogs]

Also,see if the info in this article can be of use http://www.mendipdatasystems.co.uk/r...ps1/4594533224