Deleting parent record that has no child

[constantG]

I have the following code that I use on exit from my application. It successfully deletes childless parent records, but is there a better way to do this. My code looks a bit clanky and would like to streamline if possible.

strSQL = "SELECT tblPersonal.intPersId " & vbCrLf & _
             "FROM tblPersonal LEFT JOIN tblContact ON
tblPersonal.intPersId=tblContact.intPersId " & _
vbCrLf & "WHERE (((tblContact.intPersId) Is Null));"
                    
Set rsOrphan = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set rsOrphan1 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                
                Do Until rsOrphan.EOF
                    DoCmd.SetWarnings False
                    
                    intDeletePersId = rsOrphan!intPersId
                    strSQL1 = "DELETE tblPersonal.intPersId " & vbCrLf & _
                              "FROM tblPersonal " & vbCrLf & _
                              "WHERE tblPersonal.intPersId = " & intDeletePersId
                    DoCmd.RunSQL strSQL1
                    rsOrphan.MoveNext
                Loop
                    DoCmd.SetWarnings True
                    CloseCurrentDatabase

Thanks in advance.

CW

 

[MarkK]

I believe you could do something like this ...

CurrentDB.Execute _
  "DELETE FROM tParentTable As p1 " & _
  "WHERE ( " & _
    "SELECT Count(*) " & _
    "FROM tChildTable " & _
    "WHERE ParentID = p1.ID " & _
  ") = 0;"

... which deletes them all immediately. No loop required. And if you avoid DoCmd you don't need to worry about warnings ....

 

[MarkK]

I think for your tables it would be ...

DELETE FROM tblPersonal As p1
WHERE (
  SELECT Count(*) 
  FROM tblContact
  WHERE intPersonID = p1.intPersonID 
) = 0;

... but test it carefully!

 

[constantG]

Well lagbolt it works, >.< can't beleive how short the reply was but thanks.

 

[MarkK]

Yeah, you bet. I'm constantly impressed with how powerful SQL is!!!