Solved

MS Access List All Tables that Do Not Have Relationships

Posted on 2015-01-12
7
78 Views
Last Modified: 2015-03-22
Hi,
Is there a way to list all tables in an MS Access database that do not have relationships ?
Your assistance is greatly appreciated.
Kind Regards,
Mohamed
0
Comment
Question by:Mohamed Singh
  • 4
  • 3
7 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40545274
Are you trying to identify the explicit relationships (defined in the UI) or the implicit relationships (established in queries)?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40545288
For explicit relationships, use this:
Option Explicit


Sub Q_28595093()
    Dim rel As Relation
    Dim dbRels As Relations
    Dim dicUnique As Object
    Dim tbl As TableDef
    
    Set dicUnique = CreateObject("scripting.dictionary")
    Set dbRels = DBEngine(0)(0).Relations
    For Each rel In dbRels
        If dicUnique.Exists(rel.Table) Then
            dicUnique.Exists(rel.Table) = dicUnique.Exists(rel.Table) + 1
        Else
            dicUnique.Add rel.Table, 1
        End If
        If dicUnique.Exists(rel.ForeignTable) Then
            dicUnique.Exists(rel.ForeignTable) = dicUnique.Exists(rel.ForeignTable) + 1
        Else
            dicUnique.Add rel.ForeignTable, 1
        End If
    Next
    For Each tbl In DBEngine(0)(0).TableDefs
        If dicUnique.Exists(tbl.Name) Then
        Else
            Debug.Print tbl.Name, "has no defined relation"
        End If
    Next
End Sub

Open in new window

0
 

Author Comment

by:Mohamed Singh
ID: 40553839
Hi aikimark,

Thank You responding.
My apologies for my delayed response.

I'm trying to identify tables which do not have explicit relationships.

I tried running your code but get error:

Run-time error '451':
Property let procedure not defined and property get procedure did not return an object

on line 19

Kind Regards,
Mohamed
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

Expert Comment

by:aikimark
ID: 40554019
Oops.  Please test this version:
Option Explicit


Sub Q_28595093()
    Dim rel As Relation
    Dim dbRels As Relations
    Dim dicUnique As Object
    Dim tbl As TableDef
    
    Set dicUnique = CreateObject("scripting.dictionary")
    Set dbRels = DBEngine(0)(0).Relations
    For Each rel In dbRels
        If dicUnique.Exists(rel.Table) Then
            dicUnique.Exists(rel.Table) = dicUnique.Exists(rel.Table) + 1
        Else
            dicUnique.Add rel.Table, 1
        End If
        If dicUnique.Exists(rel.ForeignTable) Then
            dicUnique.Exists(rel.ForeignTable) = dicUnique(rel.ForeignTable) + 1
        Else
            dicUnique.Add rel.ForeignTable, 1
        End If
    Next
    For Each tbl In DBEngine(0)(0).TableDefs
        If dicUnique.Exists(tbl.Name) Then
        Else
            Debug.Print tbl.Name, "has no defined relation"
        End If
    Next
End Sub

Open in new window

0
 

Author Comment

by:Mohamed Singh
ID: 40568449
Hi aikimark,
Again, my apologies for the very late response - the new year has loads of things to get done!
Thank You for this second post.
Regrettably,the same error now occurs on line 14.
Kind Regards,
Mohamed
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40568659
One more try:
Option Explicit


Sub Q_28595093()
    Dim rel As Relation
    Dim dbRels As Relations
    Dim dicUnique As Object
    Dim tbl As TableDef
    
    Set dicUnique = CreateObject("scripting.dictionary")
    Set dbRels = DBEngine(0)(0).Relations
    For Each rel In dbRels
        If dicUnique.Exists(rel.Table) Then
            dicUnique(rel.Table) = dicUnique(rel.Table) + 1
        Else
            dicUnique.Add rel.Table, 1
        End If
        If dicUnique.Exists(rel.ForeignTable) Then
            dicUnique(rel.ForeignTable) = dicUnique(rel.ForeignTable) + 1
        Else
            dicUnique.Add rel.ForeignTable, 1
        End If
    Next
    For Each tbl In DBEngine(0)(0).TableDefs
        If dicUnique.Exists(tbl.Name) Then
        Else
            Debug.Print tbl.Name, "has no defined relation"
        End If
    Next
End Sub

Open in new window

0
 

Author Closing Comment

by:Mohamed Singh
ID: 40681226
Hi aikimark,

Many Thanks - No errors.
Please excuse the very very delayed response.

Kindest Regards,
Mohamed
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now