Question about Relationships Window

[Sandra Grawunder]

I have a db with linked tables. The relationships window
shows multiple instances of the master table and it's
links to the other tables. The db works fine i.e.
queries, reports, etc.

Why does the table show up more than once. Is this a
sign of redundancy or something else I should take notice
of?

Is there a way to stop showing the redundant links in the
relationships window?

Thanks,

Sandra G

 

[Allen Browne]

Access will show a 2nd copy of a table in the Relationships window for valid
reasons if:
- a pair of tables have 2 separate relationships defined, or
- a table has a relationship to itself.
The 2nd copy will be aliased with a "_1" suffix.

It can also show a 2nd copy of the table for no valid reason if:
- you added the table to the relationships window a second time,
- there is a corruption.

To edit the actual relationships, work in the back end database (the mdb
file that contains the actual tables.) To fix the view of the relationships
you see in the front end database (the one with the attached tables), you
can delete all the tables from the Relationships window, save it, and add
the tables back again.

Suggested sequence:
1. Delete all tables from the Relationships window in the front end file.
Save. Close. Close database.

2. Open the back end database. Uncheck the Name AutoCorrect boxes under:
Tools | Options | General
Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the back end database:
Tools | Database Utilities | Compact

4. Open the Relationships window in the back end database. Click the Show
All Relationships button (toobar icon), and check that everything is correct
and no tables are unnecessarily duplicated. (You don't need to save this
view of the relationships window if you do not wish to.)

5. Close the back end database.

6. Open the front end database. Uncheck the Name AutoCorrect boxes under:
Tools | Options | General

7. Compact the database:
Tools | Database Utilities | Compact

8. Open the Relationships window. Click the Show All Relationships button
(toobar icon). Are they okay now?

If the relations are still incorrect, it is possible to programmatically
delete them all (even hidden ones), compact, and then recreate them all.
This was sometimes necessary in Access 95, and should not be needed in later
versions. If you need to code to delete all relations, it is available here:
http://allenbrowne.com/DelRel.html

 

[sandra grawunder]

I'm not sure I understand what is the back end and what
is the front end. I have a simple database on a
standalone computer.
TKS

 

[Allen Browne]

Perhaps I misunderstood when you said you have "linked tables".

That normally means the tables are in one database (mdb file) which we call
the "back end", and the rest of the objects (queries, forms, ...) are in
another database called the "front end" where the tables are attached using
File | Get External | Link.

If you just have one single mdb file that contains the tables, queries,
forms and everything, then you don't need to worry about this front end/back
end distinction. Just open the Relationships window from the Tools menu, and
follow the steps there.