Linked SQL Tables don't report Cascading Updates and Deletes

[bdp@gmail]

In the 'old days' when it was only Access, and no other server engine, whenyou deleted linked tables with cascading updates turned on it would warn you, "You're about delete this record, and a bunch of records in these othertables" or something like that. But now, with the same tables, but the relationships defined on the server, you hit delete, it deletes the record, all the associated linked records, and boom it's done! No warning! No thanks, no nothing!

How can I have Access raise a flag on linked SQL tables that have a cascading updates/deletes relationship setup on the SQL backend?

Seems like when I'm in the SQL Management Studio, whatever it's called, andI issue a query to delete like that it tells you that there are related tables, are you sure?

Any ideas?

Thanks.

-Brian P.

 

[Phil Hunt]

I am not sure about this.

You may have to set up a relationship INSIDE Access also ?


In the 'old days' when it was only Access, and no other server engine, when
you deleted linked tables with cascading updates turned on it would warn
you, "You're about delete this record, and a bunch of records in these other
tables" or something like that. But now, with the same tables, but the
relationships defined on the server, you hit delete, it deletes the record,
all the associated linked records, and boom it's done! No warning! No
thanks, no nothing!

How can I have Access raise a flag on linked SQL tables that have a
cascading updates/deletes relationship setup on the SQL backend?

Seems like when I'm in the SQL Management Studio, whatever it's called, and
I issue a query to delete like that it tells you that there are related
tables, are you sure?

Any ideas?

Thanks.

-Brian P.

 

[Tony Toews]

In the 'old days' when it was only Access, and no other server engine, when you deleted linked tables with cascading updates turned on it would warn you, "You're about delete this record, and a bunch of records in these other tables" or something like that.

I didn't like that message because it was so easy for the user to not
notice the "a bunch of records" part. Instead I handled it all
myself in code. It only takes about two lines per child table.

DELETE * FROM <table name> WHERE <ForeignID>=" & <ParentID>
Currentdb.execute strsql, dbfailonerror.

That said if there were child records I might display a count of the
number of records and get a confirmation from the user. Or I would
just flag the parent record as inactivated.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/