Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36

    Is corruption caused by idex deletion and subsequent restoration a risk?

    Situation: Parent table to mutiple child table relationship, where ID# is indexed in the parent table and present in the child tables

    ACTION: parent record is APPENDED to ARCHIVE and all its CHILDREN are also APPENDED to their own CHILD ARCHIVAL TABLES, and subsequently all records PARENT and CHILDREN are DELETED.

    If a PARENT record ( a single unique ID# ) is APPENDED back to the original table at a later date, and its corresponding Children are APPENDED back to their original Corresponding Child Tables, is there a chance for table corruption, or dysfunction when running queries?

    The way that hard drive sectors can become corrupted, maybe the database storage space for the table becomes corrupted by all this irregular table manipulation?
    Like can something weird start happening? I would die if down the road my information system started malfunctioning because because I messed around with the


    natural creation of indexing and registry assignment, etc.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Instead of 'moving' records, why not have a field in parent that flags as 'archived'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Quote Originally Posted by June7 View Post
    Instead of 'moving' records, why not have a field in parent that flags as 'archived'?
    How would your suggestion cut the table size in half?

    Back to answering my question? Anyone else out there that understands the question, or do I need to re-post- since the last guy chalked up a "reply"

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    scissors are always an option - or a chainsaw

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    "Cut" table in half by applying filter criteria to exclude the "archived" records. Life will be simpler and corruption would not be a concern. Are you using autonumber key for linking parent/child records? If so, then yes, corruption and dysfunction is a distinct possibility.

    Re-posting the question would not be appreciated as that would be a duplicate. This is a discussion style forum and I contributed to the discussion. if you want a Q&A style, visit StackOverflow.com. However, that forum is focused on assisting with code issues, not concepts where responses would be more of an opinion.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    My opinions.

    Regarding #1
    Correct if you do not use autonumbers as your primary keys. See below.

    Regarding #2
    You cannot dictate the value of an autonumber when appending a record. Any new records get a new autonumber.
    So, if you are managing your own key system (no autonumbers) this could work.
    If the selection criteria pulls a child record with a parent key pointer to a parent that doesn't exist, that append will obviously fail.

    Regarding #3
    Yes, no problem if duplicates OK, same as your original child tables. No autonumbers.

    Regarding #4
    You are presenting a scenario of complex data interaction without enough detail for me to form an opinion.

  7. #7
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Thank you Davegri. . . . you are bringing to my attention issues I did not recognize, and still don't fully understand. Am I misunderstanding you? Please read:

    Indeed the ID# field is AUTONUMBER and INDEX says "NO"
    The only unique value across tables, both PARENT and CHILD is AUTONUMBER, and it is not indexed.

    So I think you are trying to make me understand that when I APPEND those 25,000 records to the ARCHIVE table, they will take their autonumber with them, but when I try to restore individual PARENT RECORDS back to the original PARENT TABLE ACCESS will over write the ID# field with a new autonumber? Therefore when the Children are restored via APPEND to their respective original tables, their ID# fields will also overwrite and no longer match up to the PARENT?

    EXAMPLE:

    a unique PARENT RECORD is ID#: AUTONUMBER 5, with seven Children in Table 2 having TABLE2-ID# AUTONUMBER 5, and four Children in Table 3 having TABLE3-ID# AUTONUMBER 5

    In their respective ARCHIVE tables they do in fact retain AUTONUMBER 5
    But when APPENDED back to the original table, ACCESS will over write:

    PARENT 5 is overwritten to 54964
    Child table 2 5 is overwritten to 12945
    Child table 2 5 is overwritten to 26747

    The data is now scrambled. Is that what you are trying to show me?

    I physically have to make the PARENT Table half the size and I do not want to throw away data. I suppose I could create a new GUI to interface with the ARCHIVE
    Database. I think you are saying the data WILL NOT BE SCRAMBLED if I call up information using the ARCHIVE tables. That the AUTONUMBERS in the ID# field will remain 5, and there will be no scramble. This is inconvenient but safe.

    I just have to look at the data with a second GUI and avoid APPENDING back. Is that correcT?

    Maybe I could write a Macro to view GLOBAL information (ARCHIVED and ORIGINAL combined)
    That is to write a query against the ARcHIVE TABLE for lastname, first name, dob and pull records into a MAKEtemp table; Then do the same from
    the CURRENT ORIGINAL table, then APPEND one to the other and display. I will then see information from the beginning of time for a Lname, Fname, dob combination. Is that making sense?

    This information management system of 50,000 records was built over ten years ago and has functioned for my purposes fantastically. There is an intricate GUI,
    including flat files created through REPORTS which are exported for billing purposes, lots of query reports, etc. So I don't want to start corrupting anything. Unfortunately for me, while it speeds along on a DELL XPS15 with 8 RAM in Windows 7, moving from record to record through the GUI is very slow on an XPS12 whether I use WIN 7 or WIN 10. I found that if I delete 50% records from the PARENT table, the GUI functions fine on the XPS12.

    There is no unique element to the information.
    DATE of SERVICE is not unique
    Person is typically unique, but I wanted Person/date of service (DOS) to be unique for internal/industry reasons - therefore lastname firstname dob is redundant by
    A Person can have multiple ID#s from year to year

    Therefore the only unique value is Autonumber, I am not sure why anymore that I did not index that field, and each CHILD RECORD shares that autonumber with its PARENT.





    Quote Originally Posted by davegri View Post
    My opinions.

    Regarding #1
    Correct if you do not use autonumbers as your primary keys. See below.

    Regarding #2
    You cannot dictate the value of an autonumber when appending a record. Any new records get a new autonumber.
    So, if you are managing your own key system (no autonumbers) this could work.
    If the selection criteria pulls a child record with a parent key pointer to a parent that doesn't exist, that append will obviously fail.

    Regarding #3
    Yes, no problem if duplicates OK, same as your original child tables. No autonumbers.

    Regarding #4
    You are presenting a scenario of complex data interaction without enough detail for me to form an opinion.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    PARENT 5 is overwritten to 54964
    Child table 2 5 is overwritten to 12945
    Child table 2 5 is overwritten to 26747
    The child table above CANNOT possibly have two records with the same autonumber, 5. Access will not allow duplicate autonumbers in a table. Also, a table is allowed only ONE autonumber FIELD to be defined.
    So it's murky to me just how your tables are designed. I have never seen foreign keys defined as autonumbers, and can't imagine how that could work.

    The child table links (foreign keys) pointing back to the parent CANNOT be an autonumber in the original tables. As stated, autonumbers cannot be dictated. Those links in the child tables have to be Long Integer and will not change when archived or restored.
    Without seeing your relationship diagram, I don't understand the function of the autonumber (not the links back to the parent, these are traditionally called Foreign Keys) fields in the child tables at all.

    One way of achieving you goal with safety without manipulating the keys:

    Copy the entire database as a duplicate with a new name.

    Open the new database and delete all the child records that you want to keep in the original. Delete all the parent records that you want to keep in the original. This will leave you with only the records you want archived.

    Then

    Open the original database and delete all the child records that you wanted archived. Delete all the parent records that you wanted archived.

    Now you have two guaranteed working databases, one with active records and one with archived records.

    Another approach:
    When archiving, only archive the child records. Do not archive parents.
    Then restoring a child record is simply a matter of appending it back into the original. It will attach to the existing parent. This can only work if the foreign keys in the child records (the pointer back to the parent) are long integers (as it should be).
    This is the simplest way, and the method I would use. Tradeoff is that Parents remain in original even if they have no children. You would have to decide if this fact would present problems.


    More:
    I think you might be confused about the one-to-many table setup in Access, and about the function of primary keys, autonumbers and foreign keys.
    Have a look at this video. The words "Foreign Keys" are not mentioned until near the end, so be sure to stay tuned for the full 10 minutes.

    4. Microsoft Access 2016 Basics: One To Many Relationships - Bing video
    Last edited by davegri; 10-14-2021 at 10:12 PM. Reason: added some more thoughts

  9. #9
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Thanks for your thoughtful reply. I did watch the video. Nothing new there. You are correct that I am not utilizing standard nomenclature.

    I apologize if this below was not clear - -I thought it was at the least, obviously apparent. Allow me to try to restate, perhaps in a "clearer" fashion:

    In my parent table, I am using autonumber for the ID field as primary key, indexed (No duplicates), "required" is irrelevant
    The SHOW table displays one to many relationship to CHILD TABLE 2 & 3
    The CHILD table also have an ID field, long integer, index on (duplicates OK) not required
    I designed a GUI with nested subforms.
    The primary subform populates the PARENT TABLE.
    If a command button is clicked which opens a subform that populates a CHILD TABLE, then as soon as any value is entered into a CHILD field,
    The CHILD ID FIELD is autipopulated with the ID integer in its Parent record.
    Multiple instances of CHILD all get the same PARENT autonumber. Yes, as you say, the same "5" in the CHILD is not a primary key value and is as you say a foreign key, duplicates allowed, but the field is indexed.

    So back to my questions (now 2 questions).

    If I am going to have quarterly ARCHIVING EVENTS, I need to dynamically create the ARCHIVE.
    It seems apparent that I have to Archive the CHILDREN before ARCHIVING the PARENT and then DELETING both.
    The question is whether I can recall records from the respective ARCHIVE TABLES and APPEND back to the original.

    #1
    I do not understand why I cannot APPEND back the PARENT and CHILD records, as they all have unique autonumbers generated
    by the PARENT GUI. When they APPEND back, I would think they would sort back in sequence and be fully accessible and functional.
    Why will I corrupt the tables when I append back? The autonumbers are appended back out of sequence. Do they have an new invisible index number
    and still have an invisible old index number that are in conflict? Do they not sort back into autonumber order? Do they stay in NEW INDEX number order
    and so but wouldn't the autonumber integers still match up? And queries resort the records appropriately? What is the problem?


    #2
    The idea of doing a one time archive with no restoration capacity is obvious. I was trying to avoid it. But if I resort to it, could you suggest
    how I could write a routine that would accomplish setting an archive field to YES under the following rule:

    PARENT TABLE is normalized except for Lname, Fname, dob
    =======================================
    that is a person may or may not to have many DATEoSERVICE, however each instance of [PERSON | DATEoSERVICE] is a unique event and user requirements
    requires PERSON fields (Lname, Fname, dob) to remain un-normalized. The client does not mind the memory cost of retaining 2 text fields and a date field in swap for a long integer. So let's get past that. 15 years of data or barely 50 KB of storage. A non-issue.

    example:

    ASSUMED: Primary key is AUTONUMBER (essentially a unique event)
    NEW FIELD: ARCHIVEFLAG

    RULE: If DATEoSERVICE for a PERSON has no occurrence newer than 6 years (run today example: newer than 10/17/2014), then SET/UPDATE all PARENT RECORDS FOR THAT PERSON to ARHCIVEFLAG is YES But if DATEoService for a PERSON has at least on instance equal to or newer than 6 years then DO NOT SET / UPDATE any PARENT RECORDS FOR THAT PERSON ARCHIVEFLAG stays NO.

    THEN I could run a DELETE QUERY if ARCHIVEFLAG is YES, CASCADE DELETE the record






    Quote Originally Posted by davegri View Post
    The child table above CANNOT possibly have two records with the same autonumber, 5. Access will not allow duplicate autonumbers in a table. Also, a table is allowed only ONE autonumber FIELD to be defined.
    So it's murky to me just how your tables are designed. I have never seen foreign keys defined as autonumbers, and can't imagine how that could work.

    The child table links (foreign keys) pointing back to the parent CANNOT be an autonumber in the original tables. As stated, autonumbers cannot be dictated. Those links in the child tables have to be Long Integer and will not change when archived or restored.
    Without seeing your relationship diagram, I don't understand the function of the autonumber (not the links back to the parent, these are traditionally called Foreign Keys) fields in the child tables at all.

    One way of achieving you goal with safety without manipulating the keys:

    Copy the entire database as a duplicate with a new name.

    Open the new database and delete all the child records that you want to keep in the original. Delete all the parent records that you want to keep in the original. This will leave you with only the records you want archived.

    Then

    Open the original database and delete all the child records that you wanted archived. Delete all the parent records that you wanted archived.

    Now you have two guaranteed working databases, one with active records and one with archived records.

    Another approach:
    When archiving, only archive the child records. Do not archive parents.
    Then restoring a child record is simply a matter of appending it back into the original. It will attach to the existing parent. This can only work if the foreign keys in the child records (the pointer back to the parent) are long integers (as it should be).
    This is the simplest way, and the method I would use. Tradeoff is that Parents remain in original even if they have no children. You would have to decide if this fact would present problems.


    More:
    I think you might be confused about the one-to-many table setup in Access, and about the function of primary keys, autonumbers and foreign keys.
    Have a look at this video. The words "Foreign Keys" are not mentioned until near the end, so be sure to stay tuned for the full 10 minutes.

    4. Microsoft Access 2016 Basics: One To Many Relationships - Bing video

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Have a look at the attached. I think it replicates your table design.

    It has parent table, child table set up in conventional form-subform arrangement.
    4 append queries are included.
    2 queries to append parent and child to archives
    and
    2 queries to append archives back to parent and child.

    The query criteria for the archives and restores is key value 2 as the parent primary autonumber key.

  11. #11
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    thanks again for playing


    Your test DB is neat. . .. but


    1) I wrote my tables are indexed - yours are not.
    Will that make a difference when INSERTING BACK? Will there be corruption?



    In my parent table, I am using autonumber for the ID field as primary key, indexed (No duplicates), "required" is irrelevant
    The SHOW table displays one to many relationship to CHILD TABLE 2 & 3
    The CHILD table also have an ID field, long integer, index on (duplicates OK) not required




    2) Change Lena Gaingster to Rob Banks second occurrence
    Also, Add a 5th Parent record M.T. Pockett 10/21/2021 - - - - -this customer came back for business, today!


    now you can see why I used AUTONUMBER - -- - an Lname, Fname, Pdate combination DEFINES a unique transaction --- as does AUTONUMBER
    you are still thinking in terms of Lname, get out of that paradigm - -now you can see the relevance of the following


    ......... you set archive=2; I have a more dynamic filter for ARCHVIE
    I wrote:


    RULE: WHERE PDATE for ACROSS PERSON (not for an individual PARENT record) has no occurrence newer than 6 years (run today example: newer than 10/17/2014)


    In other words, if PDATE for a PERSON has at least on instance equal to or newer than 6 years then do not ARCHIVE


    how do I write that?


    So that Rob Banks ARCHIVES but M.T. Pockett Does not ARCHIVE

    SELECT
    priorQuery.dob,
    Last(priorQuery.fname) AS Lastfirstname,
    Last(priorQuery.Lname) AS LastLastname,
    Max(((priorQuery.PDATE))) AS PDATE,
    priorQuery.inactive
    GROUP BY priorQuery.dob, priorQuery.lname, priorQuery.lname, priorQuery.anyOTHERimportantFILTER
    HAVING (((priorQuery.anyOTHERimportantFILTER)=False))
    ORDER BY Max(((priorQuery.PDATE)));

  12. #12
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    I want the table to be physically half the size.
    I don't mind the idea of having a flag, but how do you write the routine so that we update the flag to ON and then INSERT and DELETE based on that fields value?

    something like

    SELECT
    priorQuery.dob,
    Last(priorQuery.fname) AS Lastfirstname,
    Last(priorQuery.Lname) AS LastLastname,
    Max(((priorQuery.PDATE))) AS PDATE,
    priorQuery.inactive
    GROUP BY priorQuery.dob, priorQuery.lname, priorQuery.lname, priorQuery.anyOTHERimportantFILTER
    HAVING (((priorQuery.anyOTHERimportantFILTER)=False))
    ORDER BY Max(((priorQuery.PDATE)));


    Then we match the query value to the Table to SET the FLAG value, then run an APPEND/INSERT query and a Delete Query

    But how exactly?

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Why don't you use my DB example to test your scenarios? You can find out for yourself if your proposals are valid.

    I'm very confused trying to follow the described logic of your requirements, having not seen so much as a table relationship diagram of your setup.
    This is probably true for the other responders, seeing the brevity of their posts.

  14. #14
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Quote Originally Posted by davegri View Post
    Why don't you use my DB example to test your scenarios? You can find out for yourself if your proposals are valid.

    I'm very confused trying to follow the described logic of your requirements, having not seen so much as a table relationship diagram of your setup.
    This is probably true for the other responders, seeing the brevity of their posts.
    "Maybe you want to tell me how to post my tables and my relationships instead of insulting me that I haven't "AS MUCH" showed you anything. "

    I did try to use your tables. Your tables are not quite right. For starters, your indices are incorrect. Why you insist on using your tables instead of using mine, is beyond sensibility, as you are not the community member. I even went to the length to tell you how to change your tables to meet my requirements. But you are too impatient and insensitive to put out any real effort to modify your tables.

    I really have "AS MUCH" described the situation. I created some tables that are better representation. but I do not know how to post them or cut and past SNIPS. Maybe you want to tell me how to post my tables and my relationships instead of insulting me that I haven;t "AS MUCH" showed you anything.

    I can test against them, but I don't know how to write the logic. I posted a sample of code that works for its tables quite well. But it is not going to run correctly with these test tables. By looking at the sample code I offered and reading by user defined requirements, I would hope a skilled advisor would read between the lines and get the point. But of course, if your aim is to provide 10 second answers to 20 second glances, that is not going to happen. Others are brief because they can't step up to the challenge.

    It seems these communities are not designed to help people who are not "expert " and doing this for a living every day. I don't know where a regular guy can get REAL HELP anymore. 10 years ago, there were a couple guys out there that were a huge help. I guess they don't make them like they used to. And even then, it was 2 guys not 20. Oh well.

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I am so sorry that you feel that I insulted you and that I have made too little effort to help. I still feel that I have too little information to provide significant helpful advice.

    I did try to use your tables. Your tables are not quite right. For starters, your indices are incorrect. Why you insist on using your tables instead of using mine, is beyond sensibility, as you are not the community member. I even went to the length to tell you how to change your tables to meet my requirements. But you are too impatient and insensitive to put out any real effort to modify your tables.
    I don't see how I could use your tables. I don't have them. Why don't you modify my tables to be like yours, and then test your scenarios with that? My database was an attempt to simulate your requirements in a very small scale that could be easily modified by you for quick adjustments and tests.

    Your last post provided no new insights that might help me.
    I feel that my participation here must end without further acrimony.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-16-2021, 01:44 AM
  2. Replies: 2
    Last Post: 06-23-2017, 10:49 AM
  3. Replies: 7
    Last Post: 05-07-2012, 12:00 PM
  4. Risk/Issues Database
    By glassarchitect in forum Database Design
    Replies: 1
    Last Post: 12-01-2010, 09:17 AM
  5. Finding subsequent codes
    By Rixxe in forum Queries
    Replies: 8
    Last Post: 09-15-2010, 02:44 AM

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