deleting records in recordset , best method ?

[locutus]

I have created a recordset to bring back all records that match
criteria entered into a textbox.

This will create a recordset of never more than two records, due
to the way our production line works.

Set rsReel = dbsLabel.OpenRecordset("SELECT * FROM FQReel WHERE PalletNo = '" & Trim(txtdeletepallet.Text) & "'", dbOpenDynaset)
hi
I now have a recordset containing one or two records, I now wish to delete
these records. What is the best way to do this. If there is only
one then I could do this - rsReel.delete and close the recordset
as this would delete the current record (-> ). If there are two
should I close the current recordset and then do this
delete * from rsReel where palletno= '" & Trim(txtdeletepallet.Text) & "'"
or can I do it a better way with the first rs I have open ie
rsReel.delete (move pointer)
rsReel.delete (again to zap second record).

any guidance will be much appreciated as I am looking to understand
the best way to write efficient code.
>locutus

[JorgeLedo]

If you have a recordset that you wish to completly unpopulate (eleteing all the records), why don't you close the recordset and if necessary open a blank one. I is faster than deleting records. If you really want to delete them and for better performance my experience tells me to do the following:
delete * from rsReel
Since you have populated the recordset with the data you wnat to delete.

BEWARE of zero lenght recordsets.


------------------
Jorge Ledo
[email protected]
Portugal

[Clunietp]

Let the DB handle the deletion:

If you can get the palette number that you want to delete, use SQL

delete from mytable where palletenumber = x

HTH

Tom

[JHausmann]

Tom is correct. It is more efficient to have SQL do the deletes. If you're using bound controls, refresh the control after you've done the delete. If not, close the recordset, delete, then open the recordset.