Disappearing table relationships

[Luis]

I am setting up a basic database with 6 tables. Whenever I
use the relationship tool and link up the tables, two of
the links disappear whenever I close the window. The
strange thing is that it doesn't complain whenever I close
and save the layout, but when I re-open the relationship
window the links are no longer there.

I have double checked the fields which are being linked
and they seemed to be OK (i.e. they are both of the same
type and one is a key field in one of the tables).

Can anybody help?

Thanks,
Luis.

 

[Gary Miller]

Luis,

A couple of thoughts...

Do both tables have PrimaryKeys?

Are you linking to the PrimaryKey in the parent table?

Are the linking fields the same data types? If one is an
autonumber, the corresponding one needs to be a LongInteger
Number.

Is the child field being linked Indexed?

Sounds like the Relationship window isn't happy with
something in the relationship.

Gary Miller
Sisters, OR

 

[John Vinson]

I am setting up a basic database with 6 tables. Whenever I
use the relationship tool and link up the tables, two of
the links disappear whenever I close the window. The
strange thing is that it doesn't complain whenever I close
and save the layout, but when I re-open the relationship
window the links are no longer there.

The relationships window is consistantly inconsistant, in my
experience. It doesn't always show all the relationships that exist,
tables disappear, lines move around... VERY frustrating.

What happens if you select the "Show All Relationships" button (or
menu option)? Another thing to try is to select one of the tables with
a disappeared relationship and click the "Show Direct" button. Does
this bring it back (temporarily or permanently)? How about clearing
the window entirely (this won't affect the relationships, just the
display) and adding the tables one by one?

 

[Guest]

Thanks for your replies,

Unfortunately I have tried both approaches and they do not
work. The show direct came up with many other tables
(created by queries I suppose) and did link the tables but
it was lost as soon as I closed the window. I emptied the
window and added the tables one by one and they linked,
but yet again lost their relationship...

I'm using access 97 if that is of any help.

Thanks,

Luis.

 

[John Vinson]

Thanks for your replies,

Unfortunately I have tried both approaches and they do not
work. The show direct came up with many other tables
(created by queries I suppose) and did link the tables but
it was lost as soon as I closed the window. I emptied the
window and added the tables one by one and they linked,
but yet again lost their relationship...

You may have some subtle systems table corruption. Try creating a new,
empty database and using File... Get External Data... Import to import
everything from this one. Then see if the relationship window starts
working again.

You may also want to open the VBA editor and copy and paste this
little subroutine into a new Module: it will list all the
relationships independent of the window.

Public Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, _
rel.ForeignTable, Hex(rel.Attributes)
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub