Relationship lines Access - SQL server

[Gaztry80]

Good morning !

When I create a relationship in SQL server and add them in my Access database, Access sometimes does not auto recognize the relationship.
I try to understand why this is happening. I have tried to restart sql server and relink/fresh the linked tables in access after creating the relationship. However, when I make a new query and drag the tables into the design view, i still need to link the primary keys for both tables manually. Does anybody knows what this is causing or is there a little trick to refresh?

Table1:
- CompanyID (PK)
- CompanyName

Table2:
- CompanyID (PK)
- Address

I can off course do the linking by myself, but I want to understand why some tables link automatically and some don't.
Thank you for helping!

Br.

 

[arnelgp]

why does your 2 table has same PK on CompanyID?
if table2 is dependent to Table1, then you need
to make the CompanyID on this table as FK.

 

[Gaztry80]

why does your 2 table has same PK on CompanyID?
if table2 is dependent to Table1, then you need
to make the CompanyID on this table as FK.

Thanks! I have deleted the PK on table 2, I thought that CompanyID in table2 also needed the status primary key.
Since it is the unique identifier of Table2. When I make it a foreign key, access recognizes the relationship.

 

[Pat Hartman]

Relationships exist ONLY in the database where the physical tables are housed. If you are not seeing them in Access, doesn't mean they are not active. Look at the relationship diagram in SQL Server.

Given what you are showing, there is no need for separate tables. If you want to support multiple addressess, then the address table needs it's own PK AND it needs the CompanyID as the FK. So,

Table2:
- AddressID (PK)
- CompanyID (FK)
- Address