RecordSet.Delete versus DELETE Query?

[jonno_g]

Hi All,

I'm trying to create a function to delete records from a table. I've done everything I can think of to prevent these records from being created in the first place, (short of multiple "Are you sure?" and "Have you checked xxxxx?" type message boxes, which just frustrate the users) but sometimes user error will create them.

I started out trying to use RecordSet.Delete method, but I couldn't get that to work. I think that may be related to the fact that I'm using an ODBC connection to a SQL Server back end. Can anyone shed any light on this?

I now have a DELETE query that works fine in almost all cases. The one case where it doesn't do what I want it to is where I have multiple duplicated records and I want to delete all except one. Is there a way to make a DELETE Query behave this way? Or do I need to take a snapshot of one of the records before I execute the DELETE query and then use either an APPEND query or RecordSet.AddNew?

If the latter, how would I go about adding an entire RecordSet in one hit? Is this possible?

Cheers,...Jon.

 

[Rabbie]

Prevention is going to be better than cure in this case. How are users adding the records to the table? If it is by a form then put code in the Before Update event to check if the record already exists and if so display a message and cancel the update.

 

[Galaxiom]

One way to absolutely prevent duplicate records is to use an Index (No Duplicates) on the fields in the table. But as Rabbie said, an ounce of prevention is better than any cure. Use code in the form.

Cleaning up the extra records in the existing data involves aggregate queries so the best way is to create a table with the key of the records to be deleted. This table can then be joined to the main table on the key to select the records to be deleted and it will be an updateable query.

However the actual deletion query is very slow because it uses an IN function. One trick is to Update a field on the main table through the join with the deletion list. This value is then used as a flag on the records to be deleted with a simple delete query on this field.

 

[jonno_g]

One way to absolutely prevent duplicate records is to use an Index (No Duplicates) on the fields in the table. But as Rabbie said, an ounce of prevention is better than any cure. Use code in the form.

Agreed - prevention is better than cure. I think I've prevented any future duplications with code. Records are already indexed, so they aren't true identical records, but the data in the record has been duplicated in the past in some cases.

One trick is to Update a field on the main table through the join with the deletion list. This value is then used as a flag on the records to be deleted with a simple delete query on this field.

I like that solution - I can work with that by iterating the recordset containing the duplicated records and flagging all bar one record then running a simple DELETE Query.

Thankyou!