title: Handling AddNew Key Violations ADODB.Recordset
body: <p>My previous Title caused a bit of confusion there...updated
  I've encountered this problem recently while using some VBA to bridge Excel and Access.</p>
  
  <p>So I have a spreadsheet containing a table that I need to import to an Access database.
  The table is something generic like the following.</p>
  
  <pre><code>EmployeeNumber  Unused_Field2   Unused_Field3
  1                    @@@              @@@
  2                    @@@              @@@
  3                    @@@              @@@
  </code></pre>
  
  <p>The unique key in Access is set to be the EmployeeNumber.</p>
  
  <p>My VBA Code in Excel looks like the following:</p>
  
  <pre><code>Sub test()
      Dim con As ADODB.Connection
      Dim rst As ADODB.Recordset
  
      strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\mydb.mdb;"
      strsql = "SELECT * FROM Table1"
  
      Set con = New Connection
      Set rst = New Recordset
  
      con.Open strcon
      rst.Open strsql, strcon, adOpenStatic, adLockOptimistic
  
      For i = 0 To n
          On Error GoTo Errhdl
          rst.AddNew Array("Field1", "Field2", "Field3"), Array(Range("A" &amp; i), Range("B" &amp; i), Range("C" &amp; i))
          On Error GoTo 0
      Next
  
      Exit Sub
  
  Errhdl:
  
      Debug.Print "Record" &amp; i &amp; "caused an error"
      Resume Next
  
  End Sub
  </code></pre>
  
  <p>Unfortunately the data is not of high quality and I often would have duplicate values causing a key violation all the time.
  While I though Resume Next will clear the Error allowing another "AddNew" to run, it did not.
  All the subsequent entries after one key violation will return the same Error.</p>
  
  <p>So my question is as follows: </p>
  
  <ol>
  <li>Is there away to clear the Error associated with a recordset? ( For the record I've tried to get the "Error Collection" and used
  the method Errors.Clear. That didn't do the trick. The Error Collection is a property specific to the ADO object - <a href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms675299%28v=vs.85%29.aspx" rel="nofollow">MSDN Errors Collection</a>)</li>
  <li>Can this be done without closing and re-opening the recordset?</li>
  </ol>
  
  <p>Please let me know if more clarification is needed!</p>