Delete items without a parent (1 Viewer)

[rockies1]

I have a table that looks like this:

Item	Parent
1	0
2	0
3	0
4	3
5	3
6	5
7	3
8	0

I want to be able to do the following:
Delete a record and any records that would be under it. For example, if I delete Item 3, I need to delete items 4, 5, & 7 because their Parent is 3. In addition, Item 6 must go because Item 5 was deleted.

I need this to occur for any number of levels of data. Item 3 could have any number of generations of children.

I hope this all makes snese.

Anyone know how I can accomplish this?

Thanks you!

 

[simongallop]

The way that I would approach it, is create a new table which only has one field, being Parent (primary key the field). Then make an append query where you have item and parent as fields, but make item Not visible. Type in 3 into the criteria of item and make the query a totals query. This gives you all the parent numbers.

For your second query, make it a delete query and type in the item number to delete.

Then take the first number in your new table, rerun the append query with that number, so you get any grand children and delete that number.

As append queries add to the base of a table, you ought to be able to automate the process, until you get to the bottom of the new table.

HTH

 

[Pat Hartman]

You need to create a self-referencing relationship. In the relationship window add YourTable twice. The second instance will be named YourTable_1. Draw a relationship line between the Parent field of YourTable_1 and the Item field of YourTable. Select Enforce Referential Integrity and then select Cascade Delete.

With the proper relationship defined, when you delete a "parent" record, all of its dependants (and their dependants) will also be deleted.

Be sure to make a copy of your table for testing purposes. Cascade Delete is a wonderful thing unless you delete something you didn't mean to delete.

 

[rockies1]

How, though, can I do this via code?

 

[Pat Hartman]

This isn't a trick. It is a feature of ALL RDBMS'. Why would you want to code something the database engine is designed to do? You are making work for yourself for no reason.

 

[rockies1]

Because I'm not doing it manually...

I'm doing it from a VB program...

 

[Pat Hartman]

My solution is not manual. It is sometimes called declaritive referential integrity because it is controlled by the database engine which in the case of Access databases is Jet. Therefore, regardless of how you access these tables - via Access, VB, C++, ASP, etc. using ADO, DAO, ODBC, whatever - Jet handles the deletes properly.

If your data were stored in an Oracle or SQL Server or DB2 or other RDBMS, the same technique would be available. The referential integrity would be preserved regardless of what program deleted the parent record. The database engine ALWAYS enforces referential integrity.

Therefore, you do not need to write code to replicate this process.