Access 2007 linked tables

Topic

New Reply

When using a linked table (Access to Access) I am not able to Cascade update when I establish the relationship (on the linked table side of the database). Is there a way to work around this issue?

Reply | Quote

Replies

Do it on the database that contains the actual tables, that’s where the data is. It makes total sense to me, to be honest.
In my view, the relationships should be established there, as well.

Reply | Quote

You can set cascade update in the main database, but when you establish the relationship in a linked database that option is ghosted out and not available.

Reply | Quote

You should not establish the relationship in the linked database. I see the backend database as the place where all the properties related to the data are set. The frontend database should be seen as a code layer, while the backend, should be seen as a data layer. Define data properties in the data layer.

Reply | Quote

When using a linked table (Access to Access) I am not able to Cascade update when I establish the relationship (on the linked table side of the database). Is there a way to work around this issue?

If you are trying to establish a relation between two tables in the back end database, yes, that should always be done in the back end file. I can think of no reason to do it in the frontend. If you are trying to establish a relation between a table in the back end and a table in the front end, then your only option is to create the simple relation. Access/Jet will not enforce referential integrity, cascade update, or cascade delete on this type of relation. Furthermore, you will not see the 1-1 and 1-many symbols on the relationship diagram; just a solid line between the tables.

I’ve seen people create relations in a front end database between queries, or a query and a table. That’s useful, I suppose, when you know what a not-so-obvious relationship is and you want Access to recognize the relationship whenever you bring the two objects together in another query; Access draws the relationship for you automatically because you’ve already told Access what it is. Again, there is no referential integrity, cascade update, and cascade delete option when creating the relation.

Happy computing!

Reply | Quote