Handling AddNew Key Violations ADODB.Recordset

Question

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.

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.

EmployeeNumber  Unused_Field2   Unused_Field3
1                    @@@              @@@
2                    @@@              @@@
3                    @@@              @@@

The unique key in Access is set to be the EmployeeNumber.

My VBA Code in Excel looks like the following:

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" & i), Range("B" & i), Range("C" & i))
        On Error GoTo 0
    Next

    Exit Sub

Errhdl:

    Debug.Print "Record" & i & "caused an error"
    Resume Next

End Sub

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.

So my question is as follows:

1. 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 - MSDN Errors Collection)
2. Can this be done without closing and re-opening the recordset?

Please let me know if more clarification is needed!

Answer 1

So I found a solution if a Recordset.AddNew encounters and error. The trick is to use CancelUpdate - MSDN

One can also use the Status property to check if the operation was successful.

a sample code would be:

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" & i), Range("B" & i), Range("C" & i))
        On Error GoTo 0
    Next

    Exit Sub

Errhdl:

    Debug.Print "Record" & i & "caused an error"
    If rst.Status <> 0 Then
        rst.CancelUpdate
    End If
    Resume Next

End Sub