Tabel relationships window

[Craig Ugoretz]

This is maybe hard to explain, but I have table relationships in mind such as
one-to-one or one-to-many between tables in my database. I enter the
relationships in the table relationships window, but I notice in certain
instances I can not create relationships between tables uinless I create
copies of tables. After I create the copies, exit the table relationships
window, and come back, tables and/or relationships disappear, or extra
relationships appear. This appears very weird. Futhermore, if I delete all
the tables in the window and start over with displaying new tables, the
relationsiphs previously entered between the tables come back. That too is
weird, in my opinion. I would like things to "stay put".

 

[Allen Browne]

Craig, this could be due to a Name AutoCorrect error, which causes Access to
get confused about the tables and fields.

Make sure you have unchecked the Name AutoCorrect boxes under:
Tools | Options | General
In Access 2007, it's:
Office Button | Access Options | Current Database
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

After that, the tables should stay in place, unless some relationships are
actually corrupt, or you rename the tables. If necessary, you can
programmatically delete all the relationships with this code:
http://allenbrowne.com/DelRel.html
Then compact, and recreate them.

You only need to use a second instance of a table (aliased by Access with a
"_1" suffix) where you have multiple relationships between 2 tables, or
self-joins. Access may not retain these if you use them unnecessarily.

 

[Jeff Boyce]

Craig

In addition to Allen's suggestions, the way in which you "delete" may be why
you are seeing what you're seeing.

In the relationships window, deleting a table doesn't delete the table's
relationship(s). You have to delete the relationship (joining lines) before
deleting the table, or it won't "stay put".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Pat Hartman]

If you have multiple relationships involving the same table, you may need to
"copy" the table to create the additional relationship. Don't worry, this
doesn't actually duplicate the table. It is just for convenience in the
relationship window and in the QBE also. Subsequent copies of a table have
a numeric suffix so you can identify them as copies for relationship
definition purposes only.