deleting records recursively

[SunWuKung]

I am trying to delete a record and all of its children recursively, but I had no success so far. Below is what I tried.

Thanks for the help.
SWK

Public Sub DeleteJobGroupHeader(JobGroupHeaderID As Integer)
Set cn = CurrentProject.Connection

'Find all the children
SQLstring = _
"Select JobGroupHeaderID from JobGroupHeader Where JobGroupHeaderParentID =" & JobGroupHeaderID
Set rst = cn.Execute(SQLstring)

Do While Not rst.EOF 'if it has children find them
DeleteJobGroupHeader rst!JobGroupHeaderID
rst.MoveNext
Loop

'if it doesn't have children delet it
SQLstring = _
"Delete From JobGroupHeader Where JobGroupHeaderID=" & JobGroupHeaderID
cn.Execute (SQLstring)

End Sub

 

[Rich]

Why not just use the Cascade Delete Related Records option on the Relationship window?

 

[SunWuKung]

I've got SQLServer2000 as the backend and as far as I can see you can't have recursive cascade because any one table can appear only once in a cascade path.

I could do it with triggers, but I don't like them.
Besides that way all sorts of other problems would come up (eg. if I have a BeforeDelete trigger on the table than it cant participate in any cascade relationship at all) and so on.