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
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
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.
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)
For performance reasons there is not really a choice: SQL server.
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.
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