Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2007 linked tables

    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?

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  3. #3
    New Lounger
    Join Date
    Mar 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  5. #5
    New Lounger
    Join Date
    Mar 2013
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post

    linked table/query limitations

    Quote Originally Posted by BKreft View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •