×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Deleting a RecordSet

Deleting a RecordSet

Deleting a RecordSet

(OP)
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).

CODE

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

RE: Deleting a RecordSet

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

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close