Databases
StackOverflow2013
Postsdbo
POCode is OK in DAO but gives run-time error '3021' in ADO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCode is OK in DAO but gives run-time error '3021' in ADO
    primarykey
    data
    text
    <p>This occurs after replacing DAO with ADO in a VB6 project that uses an Access 97 database.</p> <p>It occurs on the line marked 'Error Here in the following snippet:</p> <pre><code>If rset.BOF &lt;&gt; True Or rset.EOF &lt;&gt; True Then rset.MoveLast Do While rset.BOF &lt;&gt; True rset.Delete If rset.BOF &lt;&gt; True Or rset.EOF &lt;&gt; True Then rset.MoveLast 'Error Here End If Loop End If </code></pre> <p>Run-time error '3021' gves this explanation :</p> <blockquote> <p>"Either EOF or BOF is True, or the current record has been deleted. requested operation requires a current record."</p> </blockquote> <p>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</p> <p><strong>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.</strong></p> <pre><code>If rset.BOF &lt;&gt; True Or rset.EOF &lt;&gt; True Then rset.MoveLast Do While rset.BOF &lt;&gt; True rset.Delete If rset.BOF &lt;&gt; True Or rset.EOF &lt;&gt; True Then End If If rset.BOF &lt;&gt; True Or rset.EOF &lt;&gt; True Then rset.MoveLast End If Loop End If </code></pre> <p>Can someone explain this? (and ideally offer a solution that does not involve changing code !)</p> <p><strong>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 <em>did</em> work :</strong></p> <pre><code>If rset.BOF &lt;&gt; True Or rset.EOF &lt;&gt; True Then Do While rset.RecordCount &gt; 0 rset.MoveLast rset.Delete Loop End If </code></pre> <p><strong>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.</strong></p> <pre><code>Do While rset.EOF &lt;&gt; True rset.Delete rset.MoveFirst Loop </code></pre> <p><strong>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</strong> </p> <blockquote> <p>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.</p> </blockquote> <p><strong>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</strong></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjjb
    1. USjjb
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCode is OK in DAO but gives run-time error '3021' in ADO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCode is OK in DAO but gives run-time error '3021' in ADO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy in the world are you doing deletions by walking a recordset, instead of by issuing a SQL DELETE statement?
      singulars
      1. POCode is OK in DAO but gives run-time error '3021' in ADO
      1. USDavid-W-Fenton
    2. COmaybe because he thinks he is free to use the delete method of the recordset object?
      singulars
      1. POCode is OK in DAO but gives run-time error '3021' in ADO
      1. USPhilippe Grondier
    3. CO@Phillippe Exactly! It did its job well for many years too until I have tried to move from DAO to ADO.
      singulars
      1. POCode is OK in DAO but gives run-time error '3021' in ADO
      1. USjjb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload