none
View table Relationship of Linked Table. RRS feed

  • 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

    Friday, August 4, 2017 11:46 AM

All replies

  • 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.
    Friday, August 4, 2017 12:59 PM
  • select * from sys.foreign_keys

    There is no way to see the diagrams from Access.


    -Tom. Microsoft Access MVP

    Friday, August 4, 2017 1:48 PM
  • 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

    Friday, August 4, 2017 2:50 PM
  • 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

    Friday, August 4, 2017 3:53 PM
  • No, of course not, but this would give you a starting point for your investigations with a search engine.

    -Tom. Microsoft Access MVP

    Friday, August 4, 2017 6:29 PM
  • 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 MSDNFSF@microsoft.com.

    Monday, August 7, 2017 4:21 AM
    Moderator