Deleting a RecordSet 1

[RSfromCO]

Using an Access project connected to a SQL Server database, I have a very simple ADODB Recordset (with a single record). I am trying to DELETE the single record. The code executes OK and does in fact delete the record, but I get a run-time error saying... "Row cannot be located for updating. Some values may have been changed since it was last read." Error number is -2147217864.

What in the world? rs.RecordCount is showing 1 (which is what I'm expecting).

Dim rs As New ADODB.Recordset
Dim strSQL As string

strSQL = "SELECT * FROM MyTable WHERE ID = 7;"
If rs.State = adStateOpen Then rs.Close
rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

If rs.RecordCount > 0 Then
   rs.MoveFirst 'probably not necessary
   rs.Delete
End If

 

[jordanking]

you would be better off to just use delete statement in your sql

then you don't need the recordset at all
try this

strSQL = "DELETE FROM MyTable WHERE ID = 7"
Docmd.runSQL strSQL

if that works then you can generate the string dynamically setting the id to a different value each time.

But I would suggest creating a stored procedure to take an ID parameter, the ID, so that is can be used for any record. This increases security and means you can call the code from anywhere in your project that you need it.

.....
I'd rather be surfing