delete error

[berry]

Hi all...

I want to delete the 5 years ago's databases and there is an error occur in the line "rs.delete" . Below is my code.

The error is:
"Row cannot be located for updating. Some values may have been changed since it was last read."

This error will occur when rs reach the row that it should be deleted. What should I do?? Thanks for help.

  rs.MoveFirst
    
    Do While (rs.EOF <> True)
        If (DateDiff("yyyy", rs.Fields("dateVar"), Now()) >= 5) Then
    'MsgBox DateDiff("yyyy", rs.Fields("dateVar"), Now())
            rs.delete
        End If
        'rs.Update
        rs.MoveNext
    Loop

[Dököll]

"Row cannot be located for updating. Some values may have been changed since it was last read."

  rs.MoveFirst
    
    Do While (rs.EOF <> True)
        If (DateDiff("yyyy", rs.Fields("dateVar"), Now()) >= 5) Then
    'MsgBox DateDiff("yyyy", rs.Fields("dateVar"), Now())
            rs.delete
        End If
        'rs.Update
        rs.MoveNext
    Loop

Greetings, berry!

Are you able to read the data you're hoping to update/delete?

[berry]

Greetings, berry!

Are you able to read the data you're hoping to update/delete?

Hi Dokoll,
Thank you for your reply.
Ya, it can read the data. It can calculate the date different within the date in database and today's date. So it means that the row can be read right??
But when reach to the rs.delete, the error will occur.
I wonder what's the problem over there..
Hope can get the answer..
Thanks...

[Killer42]

Doesn't it automatically move to the next record after deleting? If so, then you'd need to skip the MoveNext. Otherwise, I don't know what the problem is. Perhaps the underlying data was changed by another process while you were moving through this recordset.

[Killer42]

rs.MoveFirst
Do While (rs.EOF <> True)
  If (DateDiff("yyyy", rs.Fields("dateVar"), Now()) >= 5) Then
    'MsgBox DateDiff("yyyy", rs.Fields("dateVar"), Now())
    rs.delete
  End If
  'rs.Update
  rs.MoveNext
Loop

I was just thinking, wouldn't it be better to use a simple delete query to do this? Something like:
DELETE FROM tablename WHERE dateVar >= #your-cutoff-date#

[berry]

I was just thinking, wouldn't it be better to use a simple delete query to do this? Something like:
DELETE FROM tablename WHERE dateVar >= #your-cutoff-date#

Hi killer42,
I can't use this code because i need to compare to the date in the database, I don't know the cut-off-date.