Code is OK in DAO but gives run-time error '3021' in ADO

This occurs after replacing DAO with ADO in a VB6 project that uses an Access 97 database.

It occurs on the line marked 'Error Here in the following snippet:

If rset.BOF <> True Or rset.EOF <> True Then
  rset.MoveLast 
  Do While rset.BOF <> True
     rset.Delete
     If rset.BOF <> True Or rset.EOF <> True Then  
         rset.MoveLast    'Error Here
     End If
   Loop
End If

Run-time error '3021' gves this explanation :

"Either EOF or BOF is True, or the current record has been deleted. requested operation requires a current record."

It appears that after the last record in the recordset is deleted BOF +/- EOF is still false in the ADO version of the code while both are true in the DAO version. If I alter the code like this it works

EDIT : the following code does NOT work. When I tried this 'solution' all the records were already deleted from the table, therefore the entire block was bypassed and so no error was thrown. Sorry for the bad information.

If rset.BOF <> True Or rset.EOF <> True Then
      rset.MoveLast 
      Do While rset.BOF <> True
         rset.Delete
         If rset.BOF <> True Or rset.EOF <> True Then 
         End If
         If rset.BOF <> True Or rset.EOF <> True Then  
             rset.MoveLast    
         End If
       Loop
End If

Can someone explain this? (a开发者_JS百科nd ideally offer a solution that does not involve changing code !)

FURTHER EDIT : As far as I can tell having tried lots of ways, the BOF and EOF are useless in ADO for testing for an empty recordset after a delete. This is because you need to use one of the move methods to update the BOF/EOF and if the recordset is empty you'll get an error. On the recordset I used, the RecordCount property was updated after a delete. Although I am not going to use it, this code did work :

If rset.BOF <> True Or rset.EOF <> True Then
   Do While rset.RecordCount > 0
      rset.MoveLast
      rset.Delete
   Loop
End If

Edit ( 1 month later) So after rewriting a whole pile of code I came across a loop containing a delete that didn't give an error. The only difference was that this loop was deleting the first record in the recordset each time rather than the last and therefore after each delete it executed a MOVEFIRST rather than a MOVELAST. So I whittled the code down as much as possible and the following snippet works fine on both Postgresql and Access, the 2 databases I tried it on, from VB6 using ADO.

Do While rset.EOF <> True
     rset.Delete
     rset.MoveFirst
Loop

After the deletion of the final record EOF and BOF still remain false but you won't get an error calling the MOVEFIRST method. Calling MOVEFIRST now sets both EOF and BOF to true and the loop is exited. By contrast, if the MOVEFIRST method is replaced with a MOVELAST then the (3021) error occurs on both databases. In his answer rskar quoted this from MSDN

Use the MoveNext method to move the current record position one record forward (toward the bottom of the Recordset). If the last record is the current record and you call the MoveNext method, ADO sets the current record to the position after the last record in the Recordset (EOF is True). An attempt to move forward when the EOF property is already True generates an error.

They mention MOVENEXT causing an error and I suppose since MOVELAST is movement in the same direction this explains why it also causes an error. I just wish I hadn't assumed that all the move methods would result in similar problems

See http://msdn.microsoft.com/en-us/library/ms675787(v=vs.85).aspx:

If you delete the last remaining record in the Recordset object, the BOF and EOF properties may remain False until you attempt to reposition the current record.

Since ADO isn't DAO, differences in behavior shouldn't be too surprising. It would seem that a call to MoveLast may trigger an update to BOF/EOF. I think you may be forced into coding changes.

For kicks and giggles, give this a try (don't know if it'll work):

rset.Delete 
rset.MoveNext
If rset.BOF <> True Or rset.EOF <> True Then                 
    rset.MoveLast               
End If

Per http://msdn.microsoft.com/en-us/library/ms677527(v=vs.85).aspx:

Use the MoveNext method to move the current record position one record forward (toward the bottom of the Recordset). If the last record is the current record and you call the MoveNext method, ADO sets the current record to the position after the last record in the Recordset (EOF is True). An attempt to move forward when the EOF property is already True generates an error.