Results 1 to 7 of 7
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Relationships with SQL backend / Access frontend

    Quick question...if use SQL server as a backend for my tables and then MS Access as a front end, where should I build out the tables' relationships? SQL Server or Access?



    Thanks

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Do it in SQL server. access wont be able to enforce referential integrity.

    For convenience purposes you can rebuild them in access as well. It'll be helpful if you're building queries in access' query designer so access will know how to the link the tables up. Other than that it doesn't matter if you do it in access.

  3. #3
    madpiet is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Quote Originally Posted by templeowls View Post
    Quick question...if use SQL server as a backend for my tables and then MS Access as a front end, where should I build out the tables' relationships? SQL Server or Access?

    Thanks
    I'd do it in SQL Server. Then it will *always* be enforced, no matter how you access the data. (Not even sure you can enforce it in Access)

  4. #4
    madpiet is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Quote Originally Posted by madpiet View Post
    I'd do it in SQL Server. Then it will *always* be enforced, no matter how you access the data. (Not even sure you can enforce it in Access)
    ( I would also create my queries / stored procedures etc in SQL Server too... T-SQL has so many more options than Access, it's just not even funny)

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    For performance reasons there is not really a choice: SQL server.

  6. #6
    fronflower is offline Novice
    Windows 10 Access 2019
    Join Date
    Aug 2023
    Posts
    3
    There are a few different ways you could set up the relationships between tables when using SQL Server as the backend and Access as the front-end:

    • Define all relationships only in SQL Server. This allows you to enforce referential integrity at the database level. You can then link the tables into Access and query them there without worrying about data integrity issues. The downside is relationships aren't visually editable in Access.
    • Define all relationships only in Access. This allows you to easily view and modify the relationships in the Access graphical interface. However, referential integrity will not be enforced at the database level. Data anomalies could occur if edits are made in Access that violate relationships.
    • Define relationships in both SQL Server and Access. This gives you the benefits of referential integrity enforcement in SQL Server and easy relationship management in Access. The downside is having to synchronize any relationship changes between the two.
    • Define primary key/foreign key relationships in SQL Server and any non-enforced relationships in Access. This gives referential integrity for key mappings while allowing flexibility for ad hoc relationships only needed in Access.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Should be clarified that relationships can only be created in the db that contains the tables. For the same reason you cannot create a relationship between tables in different db’s. This applies to any db, not just access or sql server.

    yes you can go into the relationships window in access and create joins on the linked tables - but they won’t be relationships

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access DB Backend / Excel Frontend
    By Entellex in forum Database Design
    Replies: 5
    Last Post: 11-08-2017, 10:51 AM
  2. Replies: 1
    Last Post: 08-01-2013, 09:11 AM
  3. Replies: 2
    Last Post: 05-21-2013, 02:25 PM
  4. Access Switching to frontend and backend
    By caliskier in forum Access
    Replies: 4
    Last Post: 11-19-2012, 11:58 AM
  5. Passwording frontend / backend Access 2010
    By erkwong in forum Security
    Replies: 0
    Last Post: 01-30-2012, 01:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums