Playing around with MS Access as a frontend I noticed that relationships don't work betweeen local tables and linked ones (ODBC <> MySQL) which makes sense as they are different databases, but testing a form I decided to source them to linked remote tables and they still work fine even after deleting the local Access tables and the relationships I used while creating the form. I used local tables for testing only.

Even without actually creating the relationships inside Access for the linked tables. The local ones required them, but the remote ones don't, form still works. The linked SQL tables do have the proper foreign keys on the server side. My question is why would someone create a relationships inside Access for linked tables or why does the GUI even display that option for linked tables? I don't see a purpose or the reason for having a relationships created in Access for linked tables or is there something in particular that requires them?

CodePudding user response：

No, they are not required. The "settings" or so called Mr. Boss in this regards is of course always the back end database.

So, such "diagrams" really don't matter. However, there is one perhaps "bonus" is that if you do bother to setup in the front end?

Then when you fire up the query builder, and say drop in those 2 related tables, then access will setup and "assume" and draw in the correct relationships in the query builder. Of course the query builder does not "enforce" or really have much to do with "enforced" relationships , but it can be handy. (I mean, you can always build a query - even between say a city column that not even setup as a relationship).

So, the easy way to remember how this works? Well, think what would happen if you had two different front ends - and they had different relation settings? Who would be the boss and which front end would control this?

Answer: none - it is ALWAYS the back end that is the "boss" here.

So, you don't really have to change, setup, or "maintain" the relationships in the front end. They are in effect a pretty picture. However, doing so can sometime help you, since the query builder will see look at those settings, and guess setup a relationships by default in the query builder.

but, the simple rule?

The back end is where you "always" must setup the relationships.