Results 1 to 5 of 5
  1. #1
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36

    Orphans with linking IDs deleted by DELETE Query

    I have an indexed main table with a record ID that is ONE to Many with several other tables.
    The main table has 50,000 records and I want to archive half of them. The other tables,
    the "many", are smaller and I do not need to archive those records.


    If I Append those 25,000 records to an ARCHIVE table for storage and then delete them from the
    main table, there will orphans in the "many" tables and their ID field will be blank.


    I also want to be able to selectively bring back and restore occasional records from the ARCHIVE,
    and append them back to the MAIN table. When I "append" them "back" to the MAIN table, they will have their ID#, but no way for those ID#s to link back up with the orphans, as they were deleted.


    1) In the "many" tables, I suppose I could copy the ID# to a dummy field, before executing the fatal delete. Then after restore, I could copy the dummy field back into the ID field and pray they hook up to the main table.


    2) Instead, I was wondering whether I can remove indexing before the fatal delete? Would removing the index, temporarily (using DROP command I suppose), influence ACCESS not to delete the ID# field in the child table?


    I can see that I really do not understand why it is necessary for the database engine to delete ID#s in the children tables, once they have been created. As a user, once I start populating children for a parent record in the main table, I give the child a name (the ID#) and why does their name evaporate if the parent is buried or goes on vacation overseas for awhile? Physically, why does that linking ID # have to be destroyed? I thought maybe because I have created an index that links the one ID to the many, and the delete action destroys the index? So I thought turning off the index immediately before the delete and then turning it back on after might work? But something tells me I am not thinking it through all the way, for reasons surrounding "wishful thinking.


    Can some one explain to me the physical nature of indexing and one to many Ids created when children are created.



    Thanks in advance and sorry for the clunky, imprecise nature of the question. I am a decent programming hack, but a hack nevertheless.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    A relationship can be set to maintain referential integrity. If you do this you are setting a rule that says a child record cannot exist if a parent record does not exist. It is one of the reasons that with a form/subform arrangement, you cannot enter data into the subform until the main form is saved (which happens automatically when you move from main form to subform) If you don't maintain referential integrity then a child record can exist without a parent - but that can play havoc with any logic that expects a parent to exist - such as being able to enter invoice lines without a parent invoice header.

    So, quite simply it's your app, your rules, you decide if you want or need to maintain integrity. You could drop the index (or remove the referential integrity constraint) and delete the parents, but you will not then be able to reinstate the integrity.

  3. #3
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Are you saying that once I turn off the index, I cannot turn it back on because the orphans that still have their ID#s in the indexed field will have no parent and ACCESS Dbase manager is not going to let them flounder around. If I run queries or whatever, orphans can muddle results if the index is turned back on after the parents are deleted??

    Is it true that conventional wisdom would tell me to:
    1) to ARCHIVE all the children Tables at the same time I archive the Parent table? If I ever "restore" a parent to the Main table in the future using APPEND, then I can also
    "restore" the children to their tables
    2) if I archive the child table records before I delete the parent, their indexed ID field will still have a value to archive?


    It is ok to have an ARCHIVE of "many" child records with identical values in the indexed key field, right? In the future, I would restore them using Lname AND Fname AND DOB, not the key field value.

    Once I "restore " the parent and the children, will the index automatically reinstate and link up the parent and child as if I never archived them in the first place.?

  4. #4
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    I have another follow up question. You cautioned against turning off the index because I don't want children floating around without parents.

    Well, let's look at want happened in my test database. I used MAKE Query to create an ARCHIVE TABLE and then APPENDED PARENT records with key field ID# which were older that 2006 to it. I never turned off indexing

    Then I DELETE QUERIED those records from the ORIGINAL PARENT TABLE.

    WHEN I looked inside the CHILD TABLES, there are records with BLANK ID# key field (index key)

    ACCESS did not BLOCK me from doing that. I wound up with orphans anyway.

    So I do not understand how your reply helps me in anyway
    1) Deal with the CHILD TABLES
    2) FACILITATE the RESTORATION of the ARCHIVED records if desired in the future

    Your answer is merely a statement of the obvious. It does nothing to guide me in my development or my project goal.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'd forgotten you have a wonderful app which runs slow on slow machines and that you get offensive. So I'm not prepared to spend any more time on this.

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

Similar Threads

  1. DELETE records are not deleted
    By victorqedu in forum Access
    Replies: 2
    Last Post: 10-26-2014, 10:23 AM
  2. deleted record error after delete query
    By ck4794 in forum Queries
    Replies: 3
    Last Post: 10-12-2013, 02:04 PM
  3. delete query produce #deleted in all deleted values
    By learning_graccess in forum Queries
    Replies: 2
    Last Post: 03-31-2012, 07:20 AM
  4. #Deleted issue with Delete/Append Query
    By WendyCha in forum Access
    Replies: 4
    Last Post: 08-05-2011, 08:34 AM
  5. Replies: 3
    Last Post: 07-27-2011, 02:07 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