Cascading Updates not working

[Bagger]

I have my table relationships set up to do cascading updates and deletes, but
I have found that when I change a primary key value in the top-level table,
the update does not work and I get an error:

"Cannot perform cascading operation. There must be a related record in a
table 'tb_mod'."

I thought that the whole point of cascading updates is that it suspends
referential integrity rules long enough to update the keys in the necessary
tables. What am I doing wrong here? I'd like to explain my table structure
here, but I'm not sure how to do it in plain text. I do have cascading
updates and deletes set for each relationship down the table chain.

 

[Duane Hookom]

It sounds like you are attempting to change a foreign key value, not a
primary key value.

 

[Bagger]

The error message is verbatim. I didn't see it on that page either. It
occurs when trying to update a record in the top-level table. I think I have
a suspect relationship that I need to change or remove that may be causing
the problem. Basically, I have one table in the chain that has a one-to-many
relationship to two more tables, which in turn each have a one-to-many with
another table down the chain. The foreign keys from the two tables are both
used as part of the primary key of the third table. Seems like this is
probably what's causing a problem with the cascading update. I'm going to
experiment a bit with it today.

I'd love to have a tool that would generate and execute scripts for creating
databases in Access. That would be fantastic. It's probably possible to
create a module that would do that, but I definitely don't have time to write
such a thing at the moment.

the update does not work and I get an error:

"Cannot perform cascading operation. There must be a related record in a
table 'tb_mod'."

Is that verbatim? I don't see it on the list of related messages:

http://office.microsoft.com/en-us/assistance/HA011739511033.aspx

The above article may help you resolve the issue. If not, post back
with some more details.

I'd like to explain my table structure
here, but I'm not sure how to do it in plain text.

A good way to do this, and is considered netiquette in most (all?)
other SQL DBMS-related newsgroups, is to post SQL DLL statements (e.g.
CREATE TABLE) to create the structure and SQL DML statements (e.g.
INSERT INTO) to create test data. It is not popular in the Access
newsgroups for various reasons: not everyone speaks SQL in the first
place; not everyone creates their table structures using SQL DDL; there
is no built-in tool for retrospectively creating a SQL script that
would recreate an existing structure; Access/Jet can only execute one
SQL statement at a time i.e. even if you gave someone a script there is
not build-in tool to execute it.

Recently I have been posting my 'scripts' as a simple VBA sub procedure
which uses late binding (e.g. no references) to create a new mdb then
execute the SQL statements and show simple results in a messagebox. In
other words, it's a simple copy+paste job. I've received no feedback on
this so maybe this approach isn't appreciated either (sob, sob).

Here's an example (not that for DRI I explicitly declare NO ACTION if
I'm not using a CASCADE action):

Sub SobStory()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Pilots (pilot_ID CHAR(10)" & _
" NOT NULL CONSTRAINT pk__Pilots__pilot_ID" & _
" PRIMARY KEY, CONSTRAINT pilot_ID_pattern" & _
" CHECK (pilot_ID NOT LIKE '%[!0-9]%' AND" & _
" pilot_ID NOT LIKE '*[!0-9]*'), last_name" & _
" VARCHAR(35) NOT NULL, first_name VARCHAR(35)" & _
" NOT NULL, middle_name VARCHAR(35) DEFAULT" & _
" '{{NA}}' NOT NULL);"

.Execute _
"CREATE TABLE Earnings (pilot_ID CHAR(10)" & _
" NOT NULL CONSTRAINT fk__Earnings__pilot_ID" & _
" REFERENCES Pilots (pilot_ID) ON DELETE" & _
" CASCADE ON UPDATE NO ACTION, earnings_amount" & _
" DECIMAL(19,4) DEFAULT 0 NOT NULL, CONSTRAINT" & _
" earnings_amount__must_be_positive CHECK" & _
" (earnings_amount >= 0));"

.Execute _
"INSERT INTO Pilots (pilot_ID, last_name," & _
" first_name) VALUES ('1234567890', 'A'," & _
" 'A');"

.Execute _
"INSERT INTO Earnings (pilot_ID, earnings_amount)" & _
" VALUES ('1234567890', 20.00);"

' This will fail
.Execute _
"INSERT INTO Earnings (pilot_ID, earnings_amount)" & _
" VALUES ('9999999999', 20.00);"

' This will fail
.Execute _
"UPDATE Pilots SET pilot_ID = '9876543210'" & _
" WHERE pilot_ID = '1234567890';"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.