View table Relationship of Linked Table.

Question

I have a SQL Server Express edition. SQL-A

I have few tables in a database. Tables have relationships defined.

Created a new MS Access file and added SQL Server Database tables as Linked tables to MS Access.

MS Access is able to fetch data corrected.

I can login to SQL Server and view the relationship but would like to get the information via MS Access.

Is there any way to view the relationship between linked table from MS Access?  

Thanks in Advance.

Regards -Raj

---

Rajendra Dewani
Website : http://www.dewani.net

Answer 1

Hi, Relationships are created on the backend database, so you can only view them in SQL Server. However, someone might be able to give you some code to list the table relationships in Access. Good luck.

Answer 2

select * from sys.foreign_keys

There is no way to see the diagrams from Access.

---

-Tom. Microsoft Access MVP

Answer 3

Hi

>>select * from sys.foreign_keys

Would sys.foreign_keys also list foreign_keys details of the linked tables?

Regards -Raj 

---

Rajendra Dewani
Website : http://www.dewani.net

Answer 4

You might find the table named INFORMATION_SCHEMA_REFERENTIAL_CONSTRAINTS to be more useful. But the best thing to do would be to use SQL Server Management Studio to get the foreign key info you want.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 5

No, of course not, but this would give you a starting point for your investigations with a search engine.

---

-Tom. Microsoft Access MVP

Answer 6

Hello Raj,

You may create a stored procedure to list relationship info in the sql server, then execute in Access.

StoredProcedure [dbo].[getRelationship]

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    (sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id)
Where tp.name=@tbName
ORDER BY
    tp.name, cp.column_id

StoredProcedure [dbo].[getRelationships]

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

Reference:

https://stackoverflow.com/questions/8094156/know-relationships-between-all-the-tables-of-database-in-sql-server

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].