Deleting a Recordset

[saross]

Hi guys.
I need help in deleting a recordset. I have created a button to delete a record from a form, but the code behind it needs to delete all related records in other tables also, so it will run through several tables, select the relevant records in a recordset and delete them. THis is my code:

'DELETE FROM TBLINTEREST
strSQL = "SELECT TblInterestRelationship.* FROM TblInterestRelationship WHERE (((TblInterestRelationship.ContactID) = "
strSQL = strSQL & lngContactID
strSQL = strSQL & "));"

Set rsRecords = db.OpenRecordset(strSQL)
With rsRecords
If Not (.BOF And .EOF) Then
.Delete
End If
.Close
End With

strSQL = ""
Set rsRecords = Nothing

The thing is, it only appears to delete one of the records, not all of them. Can anyone help?

 

[namliam]

changing:
If Not (.BOF And .EOF) Then
.Delete
End If

Into:
do while Not (.BOF And .EOF)
.Delete
.movenext
loop

should do the trick.

But why not use:
currentdb.execute strSQL
To execute the delete SQL statement? Deleting all and beeing faster as a bonus.

Further if you want to use cascading deletes and stuff "simply" turn referential integrity on and deleting the main table contents will result in deleting every related record in the DB.

Regards

The Mailman

---- Edit: LOL Mile !!

 

[Mile-O]

With rsRecords 
    Do While Not .EOF.Delete 
        .Delete
        .MoveNext
    Loop
    .Close 
End With

...or something to that effect.

 

[Mile-O]

Without the .Delete bit tacked onto the .EOF line


ROFL myself namlian, now that I spot it..

 

[saross]

Thanks for that. However, the

If Not (.BOF And .EOF) Then

line checks that the recordset isn't empty before deleting the record so prevents error messages etc.

So If I use

If Not (rsRecords.BOF And rsRecords.EOF) Then
With rsRecords
Do While Not .EOF.Delete
.Delete
.MoveNext
Loop
End With
End If
.Close

That should work?

 

[Mile-O]

I'd take out the .EOF.Delete part as that was an error on my part. Just leave it as .EOF

Try it and see.

I don't see why you don't use namlian's suggestion of a DELETE query.

 

[namliam]

That would work (more or less: Note .EOF.DELETE)

However why not use the referential integrity and the SQL Delete
Faster
Cleaner
More efficient
Easier to make
Easier to maintain
Easier to document
Easier to hand over
and more if i really think about it!

Regards

Edit: LMAOROFL

 

[saross]

I can't delete all related records in all tables, some need to stay since they relate to other contacts. I assume I can pick and choose in the relationships which cascade delete down but I have so little faith in Access I thought coding it this way would be safer?

So you're suggesting I set the cascade delete relationships up and then make the SQL statement a DELETE instead of SELECT statement and simply use:

currentdb.execute strSQL

to delete the records and related ones I want to delete?

 

[namliam]

That is my suggestion right....

Be carefull tho and experiment with it if you've never used it...

Also if you have interlaying logic (not just straightforward "KILL") I might just be needed to go your way.

Anyway look at it, see if it works for ya. It'll save you a headache or 2 if if does.

Regards

 

[saross]

[...interlaying logic (not just straightforward "KILL")...
[/B]

Erm... I don't understand that? What's interlaying logic and 'KILL'?

 

[SilentBreaker]

However why not use the referential integrity and the SQL Delete

I'm with you, namliam

 

[Len Boorman]

How about

DELETE *
FROM table name
Where Some criteria;

Len B

 

[saross]

Can someone please explain what namliam meant by

'interlaying logic' and 'KILL'?

 

[namliam]

Explain: 'interlaying logic' and 'KILL'?

'interlaying logic'

You want to delete a givin record ONLY if it meets certain criteria which may be to complex to put into SQL

KILL = delete

Regards

 

[saross]

Thanks.

I've taken your advice and used Referential Integrity and execute command to delete the record and cascade the delete down.