none
SQL Server 2005 referential integrity ignored in access query designer ?

    Question

  • Hello,

    I have tons of tables with RI in SQL Server DB. When I let users to access linked tables through query designer, they dont see relationships between tables. Could you please let me know, where to enable such thing in access ?

    thanks a lot

    Tuesday, February 05, 2013 9:07 AM

Answers

  • Hello,

    I have tons of tables with RI in SQL Server DB. When I let users to access linked tables through query designer, they dont see relationships between tables. Could you please let me know, where to enable such thing in access ?

    thanks a lot

    Go to the Options>Design Objects and check the box for Allow AutoJoin. That should automatically join related tables on the Primary Key - Foreign Key fields.


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

    Tuesday, February 05, 2013 8:38 PM

All replies

  • Hello,

    I have tons of tables with RI in SQL Server DB. When I let users to access linked tables through query designer, they dont see relationships between tables. Could you please let me know, where to enable such thing in access ?

    thanks a lot

    Go to the Options>Design Objects and check the box for Allow AutoJoin. That should automatically join related tables on the Primary Key - Foreign Key fields.


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

    Tuesday, February 05, 2013 8:38 PM
  • Access to my knowledge and in fact most ODBC clients do NOT have ANY awareness of the relationships. In fact one ODBC table might be SQL server, another linked table might be FoxPro, and the third table might be on SharePoint.

    So Access is not really aware these tables are related.

    I don't think the above is a change in the last 20 years of systems based around open database connectivity (which Access is).

    So, I not sure where you see or assumed that Access will auto join tables based on open connectivity, but to my knowledge, they do not.

    You can certainly drop the tables into relationship window and draw some join lines between the tables.  The relationships diagram will remain intact if your SQL server re-link routines don't delete the table links, but just re-cycle them. However, if the re-link routine deletes the table links, then I am quite sure you lose the diagram (and thus your auto joins).

    So, no, Access for the last 20 years NEVER did pull down the join lines from SQL server and it does not know or assume the relationships that you setup on the SQL server side of things (we talking in the context of use open database connections here).

    As noted, you can after linking to SQL server bring up the relationship window and draw in the join lines – it will show them, and at that point in time then auto-join when building queries based on those linked tables will then work. So you cannot "enable" this ability, but you certainly can draw in the the join lines in the relationship window, and that will give your users auto-join.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, February 06, 2013 12:59 AM
  • Albert

    I understand what you are saying. SQL Server maintains the RI on its end. But if the foreign key is named the same as the parent primary key, AutoJoin will join the tables in the query design view. That's not the same as showing a relationship but it's better than nothing.


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

    Wednesday, February 06, 2013 4:48 PM