Problem with Ref integrity

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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?
 
Jeff Boyce said:
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>
Click to expand...

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
 
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>
 
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)
 
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?
 
So, are you saying that you wish to keep the current record, because of its
dependencies, and you want to create a new record?
Click to expand...

That's right
Do you mean a new record WITH dependencies matching the current record, or all by itself?
Click to expand...

By itself.
 
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?!
 
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.
Click to expand...

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"?
Click to expand...

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)?
Click to expand...

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?!
Click to expand...

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)
 
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??
 
Back
Top