Cascaded delete... =Could not updae:record locked
Cascaded delete... =Could not updae:record locked
(OP)
Just adding the finishing touches to a 12 months project and am getting hung up with a persistent error message. I need to run with referential integrity so I can use cascaded delete on a single table, but get this persistent 'locking' problem after I save the database and reopen it.
In principle, this is what I am doing, and I do get the error message with this example also:
Table1 (via relationships) Table1_1
ID ID
SubID subID
Text text
Table1.ID is the Master field and Table1_1.SubID is the related field in a One-to-Many relationship.
When I first create a database with this table, referential integrity works and I can cascade delete the master record and all its subordinates.
After saving the database a reopening it I get the following error message when trying to delete any master record which has subordinates:
"Could not update:currently locked"
Even new entries into the table return the same error when trying to do a cascaded delete.
I am running Office 2000 and get the same results on another PC (also with Office 2k). The application is single user.
Any clues... a bug or 'finger trouble'?
In principle, this is what I am doing, and I do get the error message with this example also:
Table1 (via relationships) Table1_1
ID ID
SubID subID
Text text
Table1.ID is the Master field and Table1_1.SubID is the related field in a One-to-Many relationship.
When I first create a database with this table, referential integrity works and I can cascade delete the master record and all its subordinates.
After saving the database a reopening it I get the following error message when trying to delete any master record which has subordinates:
"Could not update:currently locked"
Even new entries into the table return the same error when trying to do a cascaded delete.
I am running Office 2000 and get the same results on another PC (also with Office 2k). The application is single user.
Any clues... a bug or 'finger trouble'?
RE: Cascaded delete... =Could not updae:record locked
A cascading delete means you delete related records *in another table* when you delete the record in the main table.
It look's like you've created a self-join in your relationship window (joined one table to itself) so in effect you've created a loop. Access is trying to delete related records but the record it's trying to delete is the same as the main record.
Turn cascade delete off and see if it works.
RE: Cascaded delete... =Could not updae:record locked
RE: Cascaded delete... =Could not updae:record locked
So, from your example are we talking about two different tables or are we talking about a self-join?
RE: Cascaded delete... =Could not updae:record locked
What I am pinning my hopes on is the fact that Access actually allows me to make a self join, establish referential integrity with cascaded deletes and then lets me actually save the relationship... and it works, the first time at least. All other 'illegal' operations that one tries in Access are generally rejected up front.
RE: Cascaded delete... =Could not updae:record locked
I tried to duplicate your tables & relationships, creating a self-join Table1.ID to Table1_1.subID - one to many
When I tried to add the first record to Table1, I got a referential integrity error and I noticed Access had switched the relationship around joining Table1_1.subID to Table1.ID - one to many.
I think self joins are better left to queries.
I'm still wondering why you need a cascade delete one only one table is involved.
RE: Cascaded delete... =Could not updae:record locked
I am running a linked data set (tables) to front end UI(forms). I am currently using 2 tables (plus many others) to do the job in question, so I would I need to run a query on the tables that would result in an effective cascaded delete, but cannot link queries in Access. So then I tried to run the query on the UI (forms) database and link it back to the tables, but Access will not allow you to establish referential integrity in the relationship panel with linked tables.
The end result is that I want have two related tables each with serial numbers and despatch information about related products. These products can either be 'items' (table1) or related 'sub items' (table2), both of which relate back to their common entry point. If that entry is removed, or the 'item' to which 'sub items' are associated is removed, I want to cascade delete.
Two tables works fine, until I want to search for a serial number across both tables AND THEN modify the record associated with that serial number. Because that serial number could have come from either of two tables, it becomes a convoluted process to find the actual record that needs updating. I was trying to take the easy way out.
This sounds confusing I know... a picture is worth a thousand words!
RE: Cascaded delete... =Could not updae:record locked
>These products can either be 'items' (table1) or related 'sub items' (table2), both of which relate back to their common entry point. If that entry is removed, or the 'item' to which 'sub items' are associated is removed, I want to cascade delete.
Not sure what you mean by their common entry point. Perhaps a third table that has all products + product type (item or sub-item)?
Anyway to have a one to many relationship between items and sub-items I think you need two tables (not a second instance of one table)
tblItems
ItemID <- Primary Key
serial number
dispatch
etc.
tblSubItems
SubID <- Primary Key
ItemID <- Foreign Key, links 1 to many with tblItems.ItemID
serial number
dispatch
etc.
Thus, deleting a record in tblItems will cascade delete all sub-items related to it.