Results 1 to 10 of 10
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Enfrorcing referential integrity

    Okay, now I am trying to replace the missing relationships in this db. It should be easy to do.

    In fact it is easy to do. The relationships, can created by simply clicking and drawing a line.

    However, when I try to enforce referential integrity the checkbox is greyed out. It will not let me check it and of course I want
    to check it. So what is going on?

    How can I enforce referential integrity.



    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    are the tables keyed correctly?
    are the tables on a backend? what kind?

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I believe that the tables are keyed correctly. As I said the db has had many iterations and has been copied many and pasted many times. I cannot be sure, but could that have something to do with it.

    The tables are in a subfolder named data. The main folder holds the db. It works.

    Any help appreciated. Thanks in advance.

    Respectfully,


    Lou Reed

  4. #4
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    and you are working directly in the back end file - correct? .... not via the main db front.....

  5. #5
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Yes, I am. However, that file does seem to have some differences with the front file when showing the table layout. I am guessing that that
    could be the problem.

    Any help appreciated. Thanks in advance.

    Respectfully,


    Lou Reed

  6. #6
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    sanity check: whip up 2 new tables each with a couple fields, no data - but so they can be 1:many - then do a join and see if that can be done

    the pre existing data in a table can prevent the ability to option the enforce referential integrity - but it isn't greyed out - when you check it then you get a pop saying it can't be done because of rules violation

    so not sure yet what is your situation; btw - I would make the joins regardless - there is always the chance that open/close or compact - that this clears and you can then apply referential integrity..... one can live without it ultimately and deploy your own written cascade delete queries if you have to.

  7. #7
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    NTC:

    I did as you suggested and created two new tables and was able to draw a line a relation between them, and I was able to enforce referential integrity. All of this was done on the base file that only contained
    tables.

    The problem when that is carried over to the complete db. The relationship lines apparently do not go.

    Any help appreciated. Thanks in advance.


    Respectfully,

    Lou Reed

  8. #8
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Your use of terminology confuses me. It works between 2 new test tables, and so you next draw lines between 2 real tables. You're working still all in the same Table Relationship place....there is no other "complete db"...your test tables activity is in the "complete db"....

    Also - originally you said you could make the lines but just that the Enforce Integrity option was greyed out...now you say you can't make lines.

  9. #9
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I am using the backend tables part of the database. I never used the front end at any time today.
    The front end db has arrows to the left of the table names in the navigation window so I know
    that they are linked. I checked the paths, also, and they are correct. I checked the path of each table with an arrow to its left
    next to its name.

    Still the tables, but not the relationships are shown in the relationship diagram in the front end.

    That confuses me. In the past what ever I modified in the back end was reflected in the front end.

    Not this time.

    As I said the database has been copied and pasted many, many times.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed



    I

  10. #10
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    just background:
    * paths between front/back have no bearing on table relationship joins
    * the front end's table relationship diagram is not in force in a split database - it may or may not be accurate and doesn't matter - that's just a quirk of an application that can be split or not split; once split only the back end table diagram matter
    * copying a database doesn't alter its table relationship or break anything fundamentally - but the fact that it is indeed a very old db, and perhaps there is no institutional memory/control - this can help explain at least unusual aspects to an application's situation/design

    So in the back end file; you can join & enforce 2 test tables. You cannot enforce 2 existing tables (I assume you do have the join). There is no pop up message the referential enforce check box is just inactive. That is my current understanding. I would compact/repair the back end file if you have not already. I don't expect it to change anything but it might.

    One cannot introduce integrity once there is data in the tables that don't agree to the rules. That is my guess of the situation. In the "many" table are records that do not cross link/reference to the "1" table. I suppose one can attempt to locate them and delete them though I don't know the app and one should be very certain of what you are doing before removing data.

    You can live without referential enforcement as I posted earlier - all you really lose is the cascade delete capability and the need to deal with orphan records if the parent record gets deleted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Referential integrity
    By Lou_Reed in forum Access
    Replies: 14
    Last Post: 12-17-2015, 03:08 PM
  2. Referential Integrity
    By lefty2cox in forum Forms
    Replies: 2
    Last Post: 11-30-2015, 07:26 AM
  3. Referential Integrity
    By YunqHero in forum Forms
    Replies: 4
    Last Post: 12-17-2012, 05:05 PM
  4. Referential Integrity
    By Paul H in forum Database Design
    Replies: 3
    Last Post: 11-14-2011, 03:07 PM
  5. referential integrity
    By askjacq in forum Database Design
    Replies: 1
    Last Post: 10-14-2011, 03:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums