If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with Ref integrity
I have the following problem.
In a database, I want to keep records as historic, before making any changes to them (historic in the same table). One of these fields is number (primary ID) and part of one to many relationship (one side) with referential integrity ON (update, delete ON). Before pasteappend I use a macro that sets a new value to the ID and then append the new record (so that the new record keeps the old value from the Id). The problem is that due to referential integrity: 1. If cascaded updates is ON then all related records change their ID 2. if Off I can't change the ID of the historic due to presence of related records. So I can't do anything. Because the whole procedure applies to many tables, I have designed a generic macro for all I don't want to use an append query (by bypassing the primary key) and macro because I have to make one for each table. Yet I can't disable ref integrity because I need cascaded deletes. I don't want to keep the historic data to another table because I change the table structure often and because the current records are not many. Is there a solution??? I have thought some work-throughs: 1. After pasteappend focus mustn't go to appended record, so changes are made to the old one (as far as I know this is done through an append query, which doesn't suite my needs) 2. It could be done by disabling ref integrity and resume after append. It could be done easily by using a query updating mSysRelationships, but it is read only. Could it be changed to read/write??? 3. (wouldn't preffer it cause it has been since '95 I fiddled with VB). Before append programmaticaly disable ref integrity for that relationship, but I don't know DAO. Hence, how is this done? does the expression accept wildcards so as to write a generic function for all tables according with the opened form? 4. As above, disable ref int through data definition query (though I can't find a corresponding sql statement) 5. In case none of the above can be done, is there a way in an appent query, the appendto field to be calculated from an expression in a builder?? Any possible help is welcome. This issue has been bothering me for more than 3 days. PS. Forgive me for the length of the post. |
#2
|
|||
|
|||
Tanis
You've described 'how' you are trying to do something. I don't fully understand 'why' you are doing it. If you have a record you wish to keep intact as "historic", don't modify it. If you want to create a new record, without any related (1:m) records in other tables, create it. Or am I just not understanding? -- Good luck Jeff Boyce Access MVP "Tanis Dimitrios" wrote in message ... I have the following problem. In a database, I want to keep records as historic, before making any changes to them (historic in the same table). One of these fields is number (primary ID) and part of one to many relationship (one side) with referential integrity ON (update, delete ON). Before pasteappend I use a macro that sets a new value to the ID and then append the new record (so that the new record keeps the old value from the Id). The problem is that due to referential integrity: 1. If cascaded updates is ON then all related records change their ID 2. if Off I can't change the ID of the historic due to presence of related records. So I can't do anything. Because the whole procedure applies to many tables, I have designed a generic macro for all I don't want to use an append query (by bypassing the primary key) and macro because I have to make one for each table. Yet I can't disable ref integrity because I need cascaded deletes. I don't want to keep the historic data to another table because I change the table structure often and because the current records are not many. Is there a solution??? I have thought some work-throughs: 1. After pasteappend focus mustn't go to appended record, so changes are made to the old one (as far as I know this is done through an append query, which doesn't suite my needs) 2. It could be done by disabling ref integrity and resume after append. It could be done easily by using a query updating mSysRelationships, but it is read only. Could it be changed to read/write??? 3. (wouldn't preffer it cause it has been since '95 I fiddled with VB). Before append programmaticaly disable ref integrity for that relationship, but I don't know DAO. Hence, how is this done? does the expression accept wildcards so as to write a generic function for all tables according with the opened form? 4. As above, disable ref int through data definition query (though I can't find a corresponding sql statement) 5. In case none of the above can be done, is there a way in an appent query, the appendto field to be calculated from an expression in a builder?? Any possible help is welcome. This issue has been bothering me for more than 3 days. PS. Forgive me for the length of the post. |
#3
|
|||
|
|||
"Jeff Boyce" wrote: Tanis You've described 'how' you are trying to do something. I don't fully understand 'why' you are doing it. If you have a record you wish to keep intact as "historic", don't modify it. If you want to create a new record, without any related (1:m) records in other tables, create it. Or am I just not understanding? -- Good luck Jeff Boyce Access MVP the record is duplicated and then the 'original' records ID field is calculated to the last value + 1. so the duplicated record (to which changes are made) keeps the relatioship with the other tables (with the current record) Hope this clears things out |
#4
|
|||
|
|||
I am still not understanding the business need for duplicating the "parent"
record, but perhaps I don't need to. It sounds like you are writing a new record to whatever table you wish -- what's the question again? Jeff Boyce Access MVP "Tanis Dimitrios" wrote in message ... "Jeff Boyce" wrote: Tanis You've described 'how' you are trying to do something. I don't fully understand 'why' you are doing it. If you have a record you wish to keep intact as "historic", don't modify it. If you want to create a new record, without any related (1:m) records in other tables, create it. Or am I just not understanding? -- Good luck Jeff Boyce Access MVP the record is duplicated and then the 'original' records ID field is calculated to the last value + 1. so the duplicated record (to which changes are made) keeps the relatioship with the other tables (with the current record) Hope this clears things out |
#5
|
|||
|
|||
In the table there are some (lets say 5 entries).
If I want to make a change to a record (lets say the first) , I want to duplicate that record (to record number 6, subsequently), so that the one is kept as archive and the other is changed. Plus I want the related records to keep relationship to the 'current' record (whichever it may be). here resides the problem with referential integrity. If changes are made to the duplicate record, due to referential integrity, the relationship is lost (due searching for the new ID in the foreign tables) For changes to be made to the 'parent' I must retain or regain focus to that record after pasteappend (I don't want to use append query) "Jeff Boyce" wrote: I am still not understanding the business need for duplicating the "parent" record, but perhaps I don't need to. It sounds like you are writing a new record to whatever table you wish -- what's the question again? Jeff Boyce Access MVP "Tanis Dimitrios" wrote in message ... "Jeff Boyce" wrote: Tanis You've described 'how' you are trying to do something. I don't fully understand 'why' you are doing it. If you have a record you wish to keep intact as "historic", don't modify it. If you want to create a new record, without any related (1:m) records in other tables, create it. Or am I just not understanding? -- Good luck Jeff Boyce Access MVP the record is duplicated and then the 'original' records ID field is calculated to the last value + 1. so the duplicated record (to which changes are made) keeps the relatioship with the other tables (with the current record) Hope this clears things out |
#6
|
|||
|
|||
So, are you saying that you wish to keep the current record, because of its
dependencies, and you want to create a new record? Do you mean a new record WITH dependencies matching the current record, or all by itself? -- Good luck Jeff Boyce Access MVP "Tanis Dimitrios" wrote in message ... In the table there are some (lets say 5 entries). If I want to make a change to a record (lets say the first) , I want to duplicate that record (to record number 6, subsequently), so that the one is kept as archive and the other is changed. Plus I want the related records to keep relationship to the 'current' record (whichever it may be). here resides the problem with referential integrity. If changes are made to the duplicate record, due to referential integrity, the relationship is lost (due searching for the new ID in the foreign tables) For changes to be made to the 'parent' I must retain or regain focus to that record after pasteappend (I don't want to use append query) "Jeff Boyce" wrote: I am still not understanding the business need for duplicating the "parent" record, but perhaps I don't need to. It sounds like you are writing a new record to whatever table you wish -- what's the question again? Jeff Boyce Access MVP "Tanis Dimitrios" wrote in message ... "Jeff Boyce" wrote: Tanis You've described 'how' you are trying to do something. I don't fully understand 'why' you are doing it. If you have a record you wish to keep intact as "historic", don't modify it. If you want to create a new record, without any related (1:m) records in other tables, create it. Or am I just not understanding? -- Good luck Jeff Boyce Access MVP the record is duplicated and then the 'original' records ID field is calculated to the last value + 1. so the duplicated record (to which changes are made) keeps the relatioship with the other tables (with the current record) Hope this clears things out |
#7
|
|||
|
|||
So, are you saying that you wish to keep the current record, because of its
dependencies, and you want to create a new record? That's right Do you mean a new record WITH dependencies matching the current record, or all by itself? By itself. |
#8
|
|||
|
|||
So, you may have already worked this out, but it seems like you should be
able to create a copy of a record (less the Autonumber-assigned ID), and use that to create a new (?historical?) record. The "old" record will still have all the dependencies. The new record, will NOT have the child records (because none of them were copied, and none of them "know" the new ID). The new record WILL still have the links to any lookup tables used as foreign keys in the row. Now, for another issue... you have two rows in your table, with differing IDs. They both are identical except for the ID. What makes one of them "current" and the other "historical"? What happens if a change is made to the current one (I believe this is OK, and what you are trying to do)? But how will you prevent changes to the historical one?! -- Good luck Jeff Boyce Access MVP "Jim Tanis" wrote in message ... So, are you saying that you wish to keep the current record, because of its dependencies, and you want to create a new record? That's right Do you mean a new record WITH dependencies matching the current record, or all by itself? By itself. |
#9
|
|||
|
|||
So, you may have already worked this out, but it seems like you should be
able to create a copy of a record (less the Autonumber-assigned ID), and use that to create a new (?historical?) record. The "old" record will still have all the dependencies. The new record, will NOT have the child records (because none of them were copied, and none of them "know" the new ID). The new record WILL still have the links to any lookup tables used as foreign keys in the row. Just for that reason The 'old' record will be the current and the appended will the the 'historical' Now, for another issue... you have two rows in your table, with differing IDs. They both are identical except for the ID. What makes one of them "current" and the other "historical"? a yes/no datatype field named "current" What happens if a change is made to the current one (I believe this is OK, and what you are trying to do)? I lock the form for record view only (for changes to be made there is a button to create a new version) how will you prevent changes to the historical one?! upon the onclick event of the button, a group macro checks whether the 'current' yes/no field is set to yes. If it is so the new_version macro is run, else a message box appears informing that changes cannot be made to a historical record. PS. Onother irrelevant question: Is there any IRC channel for MS access (because I am not online allday and would prefare it) |
#10
|
|||
|
|||
Just another irrelevant question reffering to the previous PS.
Is there any way to 'export' a post or a thread to be viewed offline?? (apart from saving the page. A utility maybe?? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange problem with different networks. | Adam | General Discussion | 1 | March 13th, 2005 10:17 PM |
IE6 & Outlook Problem (Strange Problem)! | KW | Outlook Express | 4 | February 1st, 2005 08:31 AM |
Row Autofit problem Excel 2003 | Matthias Klaey | General Discussion | 0 | January 19th, 2005 05:33 PM |
Reinstalling OE... | KAR | Outlook Express | 24 | August 21st, 2004 06:52 PM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |