deleting row only from recordset

[tppradeep18]

I have a recordset which contains 10 records. when i delete the fourth row using the delete method of the recordset, the row from the actual table is also getting deleted. how can i delete the row only from recordset and not from the actual table. any help?

[DrewDog_21]

You can't. When you delete, edit, or add new records to the recordset, the changes are reflected in the table. Here I would suggest modifying your SQL statement so as to filter out the unwanted record - e.g. retrieve its autonumber and then filter or requery your recordset with a new SQL statement. With ADO use the filter method:

Dim MyID As Long

MyID = myRS.Fields("IdRECORD").Value
myRS.Filter = "IdRECORD <> " & MyID

and with DAO just change the SQL.

[Paul Warren]

The best way of deleting the record from the recordset without deleting from the table immediately is using transactions -

cnTemp.BeginTrans

rsTemp.Delete
rsTemp.Update

cnTemp.RollbackTrans

where cnTemp is a connection object and rsTemp is a recordset object. The .RollbackTrans method cancels all database operations since the initial .BeginTrans. Transactions allow you to bundle a series of database operations together but not commit them until you're happy that everything is OK.

To go ahead with the changes use .CommitTrans instead of rollback on the connection object. Remember that transactions are handled at the connection level.

Hope this helps.

[Clunietp]

You can also use a disconnected recordset....just don't reconnect back to your data source to perform the update

[tppradeep18]

thanx each one of u for u r help. it really helped me. paul's answer was very new to me and i thank him specially. for teaching new thing. thanx once again...