Deleting a Recordset (1 Viewer)

[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'?

 

[Pat Hartman]

My advice is to establish the correct relationships, enforce referential integrity, and select cascade delete (where appropriate). There is nothing wrong with how Jet implements RI. Access gets a bad rap because in certain areas it is buggy but you can count on less than 1 hand the number of known bugs that impact data. If Jet could not maintain data integrity, it would simply cease to exist. No customer would ever use it. The worst "data" disasters are caused by programmers who ignore the available declarative RI features and attempt to reproduce them with user written code and SQL. One BIG problem with the user-code method is that it is totally dependent on people using the designated process to perform an action. Whereas, if you use declarative RI, it is enforced by Jet and it doesn't matter how the database is accessed, via an Access fe, a VB fe, a web fe, via DAO or ADO or ODBC, IT DOES NOT MATTER, RI is ALWAYS enforced. You cannot bypass it.

Cascade delete will delete child records ONLY if there are no other dependent RI relationships. As an example suppose you had a table that contained customers and another that contained orders. You would establish RI between the tables. If you attempted to delete a customer, the delete would be refused if you had any related orders in the orders table. If you specify to cascade delete and then went to delete a customer, the customer and ALL orders for THAT customer would be deleted.

I am confused by your concern about cascade delete. Cascade delete only goes from the 1-side of a relationship to the many-side. Never the other way. So in the above example, deleting an order would NEVER cause a customer to be deleted because an order is the many-side of the relationship.

 

[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.