Assuming that your foreign key field is not Required, perhaps you could
execute an Update query and update it to Null:
UPDATE MyTable SET MyForeignKey = Null;
DELETE FROM MyTable;

JET actually supports cascade-to-null relationships, so you may be able to
use that instead of the UPDATE query:
http://allenbrowne.com/ser-64.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dick Watson" wrote in
message
...
I have a table that has a column related to another column. (It's a linked
list.) This relationship is defined explicitly and enforced. I want to
enforce the relationship as an integrity safety net.

Now, DELETE * FROM MyTable; doesn't work since there are related records.
(I'm trying to do this from VBA via ADO.)

I've played with the Cascade Delete setting to no avail. Setting it just
results in record getting deleted in mid deletion and the query errors.

So my questions:

1) In general, what's the best way to programmatically treat DELETE * on a
table that has an enforced relationship to itself?

2) Is there any way to get there from here with ADO? ADOX has the ability
to define the relationships but I can't find any way to control the
setting for referential integrity. My thinking was to programmatically
remove this requirement, do the delete, then re-establish this
relationship.

3) I've considered nested queries to delete from the deepest items to the
root items with a loop and so forth. Since I'm trying not to set a limit
to depth, this gets ugly and doesn't scale well. Am I missing something?

4) Are the ADO Relationship Attribute values defined anywhere?

Thoughts?

Thanks in advance!