Deleting related records

[DrewDog_21]

I am working on a routine to clean the database of all junk
data. Example: Table A and Table B have a 1 to Many
relation, and I want to delete the records from Table A that
have no entry in Table B. I could do this with two
recordsets by comparing the values, but the tables are huge
and that takes forever. I thought I could use some error
handling and Resume Next statements, but those are giving
me problems on the .Delete and .MoveNext statements.

What happens is the loop arrives at a record that has
related records, attempts to delete it, skips to the error
handler, then returns to the .MoveNext line. But then the
error happens again, and the recordset stays permanently on
that record :rolleyes:

I can't use a line like Resume SkipUpdate because I want to
perform this process on many tables. Any suggestions will
be appreciated!

On Error GoTo Database_Error

With rsMyTable
If .RecordCount <> 0 Then
.MoveFirst

Do Until .EOF
.Delete 'the code gets hung up on these two lines
.MoveNext
Loop
End If

.Close
End With

Exit Sub

Database_Error:
If Err.Number = -2147217887 Then 'record can't be deleted
Resume Next
End If

End Sub



[Edited by DrewDog_21 on 09-21-2000 at 01:50 PM]

[RIVES]

I think what you want to happen can be done with a SQL statement...What backend are you using?

Say...

delete from table1 where table1.[id] in (select table1.id from table1 left outer join table2 on table2.[id] = table1.[id] where table2.id is null)

[Edited by RIVES on 09-21-2000 at 04:13 PM]

[DrewDog_21]

I'm using Access 2000 and ADO. If it can be done with an
SQL statement then I could use something like

myCN.Execute "Delete " yadda yadda yadda

The only problem I see is that I have tables that are
related to more than one other table, and the SQL would
be rather large. I suck at joins, too, so that would also
give me a headache!

Any more insights would be greatly appreciated.