Purpose of allowing relationships with Linked Tables?

[ions]

Dear Access Expert.

Are the Relantionships with Linked Tables allowed for just diagram purposes or do they actually have some other purpose? Perhaps Automatic Relationship in Query Design Window but shouldn't this be done in the BE then?

I noticed you can't do any referential integrity.

The reason I am asking is because I wanted to relate two tables that are in seperate mdb's and apply cascading updates but I guess that is impossible.

From my observations you can only Relate Tables that reside in the same container correct? Can you explain why that limitation exists if the two related tables are both MS Access?


Thank you

 

[Galaxiom]

Tables in separate databases cannot be related. If this was allowed, what would happen if one of the databases happened to be inaccessible?

Worse still, what would happen if two different developers set relationships to the same table from two other databases and cascaded updates?

It would be chaos.

 

[boblarson]

Also, just so you know - relationships need to be set in the backend of the database and it only affects (as G.A.H. said) tables local to that backend.

And also, from your statements, I am wondering if you understand just what Cascade Updates is for. Cascade updates is only referring to the change that propagates to child tables of a master table where the primary key of the master table has changed and it changes the foreign keys in the child tables. Most of the time there is no need to have that even checked because if you have set up your primary key structure well, nothing about that key will change. That is one of the reasons I like surrogate keys as it is almost Nil the possibility of a change needing to occur with the key. If you use natural keys, there is a greater possibility that a change could occur.

 

[ions]

Thanks for your responses.

Good points G.A.H.

Bob, this database is using natural keys which is not my preference either. When I develop I use artificial keys with Compound Index if necesssary but I do see the benefits of natural keys at times. (Don't want to debate on this though.)

Can you answer the below question. I don't see any purpose why Access allowed for creating relationships with linked tables in the FE. Can you ?

>Are the Relantionships with Linked Tables allowed for just diagram purposes or do they >actually have some other purpose? Perhaps Automatic Relationship in Query Design ?>Window but shouldn't this be done in the BE then?

Thank you.

 

[Rabbie]

Can you answer the below question. I don't see any purpose why Access allowed for creating relationships with linked tables in the FE. Can you ?

>Are the Relantionships with Linked Tables allowed for just diagram purposes or do they >actually have some other purpose? Perhaps Automatic Relationship in Query Design ?>Window but shouldn't this be done in the BE then?

Thank you.

Just my 2 cents worth on this. By having the relationships set in BE it doeshelp in the Query design Window. Also in my experience you will see the BE relationships if you view relationships in the FE. It also provides useful documentation

 

[ions]

Rabbie,

I ran some tests.

TRUE: If the relationships are not in the FE then you won't get them automatically set in the Query Designer Window.

If you Link tables, Access will automatically create the relationships for you in the FE.

If you delete a relationship manually in the FE, Access will never re-create that relationship automatically again. (This also seems to apply to Imported tables.)

Suppose I delete a relationship manually, delete the table Link and then relink the table again. Access will NOT automatially re-create the relationships ever again. It appears it keeps track of what relationships you deleted and will never re-create those again. I find this very strange but I guess this is the reason for enabling creating relationships.

Personally I believe that if the tables are Linked, the relationships should always be transferred to the FE and the Linked table relationships cannot be edited / deleted in the FE.

 

[boblarson]

And the reason why it lets you do it in the frontend as well as the backend even though you need to do it in the backend and not the frontend is that the Access Team didn't make that something that the relationships can determine on its own. In fact, it would be impossible for it to determine that really because a backend can also have linked tables to another database, file, etc. So, the developer has to learn and understand to set the relationships in the backend and not the frontend (Training issue).

 

[ions]

So why did the Access team allow the creating and deleting of linked table relationships in the FE when it can cause so much confusion and is unneccessary. I think we all agree that relationships must be created in the BE and never in the FE.

Access should just automatically transfer the BE relationships along with the table Link and Lock linked table relationships down.

 

[boblarson]

So why did the Access team allow the creating and deleting of linked table relationships in the FE when it can cause so much confusion and is unneccessary. I think we all agree that relationships must be created in the BE and never in the FE.

Access should just automatically transfer the BE relationships along with the table Link and Lock linked table relationships down.

Again, I told you - Access cannot determine on its own whether it is a frontend or a backend. YOU have to determine that and set things appropriately. That is why you can create and delete linked table relationships in the frontend - because the program cannot determine what it is, so the same functionality exists in all mdb, mde, accdb, accde, accdr files. I'm sure it was determined that it wasn't worth the extra money and resources involved to try to have Access figure out what it was being used for and keep you from doing something. So, sorry but in this case you have to learn and sometimes the hard way and then you have to apply that learning. They can't do everything for you.

 

[ions]

Bob -

Propossed Building Relationship Rules:

1) If the table is a local table you can create relationships.

2) If the table is a linked table you cannot create relationships.

3) When you link a table, copy its relationships to current .mdb but lock the linked relationships down (i.e cannot be edited or deleted) Access already knows if the relationship is on linked tables because they lock down referential integrity so no extra resources needed here.

There is probably a reason why they decided to allow creating and deleting relationships on linked tables but it is not apparent to me.

Peter.

 

[boblarson]

It seems so simple to you doesn't it. But it isn't as simple to write into a program as you seem to believe. It costs money to implement features and it is highly unlikely that MS is going to see that as a high gain fix. It is a training issue. The means exists to do it right. The same goes with lookups defined in tables, fields with spaces and such (MS in their samples don't follow best practices themselves).

But, why don't you go to the Access Team Blog and submit the question and your proposal to them. Who knows, they might use it or at least do some modifications.

 

[TexanInParis]

Here are the real rules:

1) You can create Referential Integrity only between two tables in the same database. The reason is simple: RI uses indexes to do the enforcement, and an index can reference only tables in the same physical file.

2) If you link tables, Access brings forward the relationship diagram. You cannot modify relationships in the front end - you can do that only in the back end where the tables and relationships are defined.

3) You can define a relationship between linked tables to aid in query building, but you cannot define RI.

4) If you delete a relationship in a front end, as IONS has discovered, Access won't put it back if you relink the tables. Note that you're only deleting the relationship *diagram*, not the actual relationship.

Personally, I think this last point is a bug, but may have been designed that way so that you can "turn off" automatic linking in queries.