Cascaded delete... =Could not updae:record locked

[dbdave]

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'?

 

[sko]

>I need to run with referential integrity so I can use cascaded delete on a single table.

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.

 

[dbdave]

Thanks.... I have a suspicion that what you say is correct, but I am bewilderd why everything, including cascaded deletes, works OK before the database is saved for the first time. I may not have made it clear that the table in question has a second field/column that is the subordinate to the primary field/column. For a given record, the subordinate field/column never has the same ID/number as the primary field/column.

 

[sko]

When you add the same table to the relationship window multiple times, Access adds the "_1" suffix to each table name. Example: Table1, Tabel1_1, Table1_2

So, from your example are we talking about two different tables or are we talking about a self-join?

 

[dbdave]

Yes you are correct, the '_1' extension you refer to is as a result of Table1 being added to the relationship window a second time. And yes there is a self join from ID to SubID with forced referential integrity and cascaded delete.

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.

 

[sko]

I've tried to set self-joins in the relationship window before and have had intermittent record locking problems as well.

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.

 

[dbdave]

Thanks sko. Not sure why the relationship reverses your mock-up. I have tried the query approach but I run into strife. If you are interested, this is my dilema in detail...

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!

 

[sko]

One thing about this, if we get enough posts in this thread maybe someone else will jump in

>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.