A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

issues on table with relationship to self



 
 
Thread Tools Display Modes
  #1  
Old May 22nd, 2009, 05:07 AM posted to microsoft.public.access.tablesdbdesign
Dick Watson
external usenet poster
 
Posts: 25
Default issues on table with relationship to self

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!

  #2  
Old May 22nd, 2009, 09:46 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default issues on table with relationship to self

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!


  #3  
Old May 22nd, 2009, 01:19 PM posted to microsoft.public.access.tablesdbdesign
Dick Watson
external usenet poster
 
Posts: 25
Default issues on table with relationship to self

The UPDATE to Null then the DELETE will probably work. That's a great idea.
I'll give it a try!

Thanks!

"Allen Browne" wrote in message
...
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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.