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:

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)
Can this be done without closing and re-opening the recordset?

Please let me know if more clarification is needed!

I would first rename your label to something other than `Err`, this is already an object in VBA. — Andy G, Jul 26 '13 at 19:22
Can you not use `Command` instead of `Recordset` to do the `INSERT`s? — shahkalpesh, Jul 26 '13 at 19:52
@shahkalpesh yes the command.execute method would work with resumenext, however it provides less control compared to a recordset. Plus that means I will need a string constructor to construct the SQL statment which is sometimes error prone especially with different datatypes and special characters. (That's my opinion anyway) — Derek Cheng, Jul 26 '13 at 20:10
@DerekCheng: No. I don't think it is error prone. `Command` used with `Parameters` collection is a simple, error-free way to do it and there is no need to do string concatenation. [Here](http://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters) is an example. — shahkalpesh, Jul 26 '13 at 20:22
+1 Yes, the parameters (as always) take the pain out of constructing the sql. — Andy G, Jul 26 '13 at 20:27
@DerekCheng: Also, one can use `recordsAffected` parameter to figure if the record was added or not. I guess, it should throw error in case of any key violation. [Here](http://www.access-programmers.co.uk/forums/showthread.php?t=214990) is another example. — shahkalpesh, Jul 26 '13 at 20:28
@shahkalpesh thanks for showing me 'Parameters' with 'Command' but in that case, I will always have a fixed number of parameters in my code because it's reliant on how the 'CommandText' is set up. I reuse the same code for inserting records to various databases with different number of fields and values, in this case then isn't the 'Recordset' more flexible? (i'm getting sidetracked here..) at the end I'm looking for an ADODB.Recordset solution. — Derek Cheng, Jul 26 '13 at 20:41
@DerekCheng: If you are looking to do things with recordset, INSERT only if the record does not exist (`SELECT COUNT(*) FROM myTable WHERE ID = newIDThatIWantToInsert`), record exists if the value is >= 1 and not otherwise. This is better than waiting for error to be raised and then deciding the path. — shahkalpesh, Jul 26 '13 at 20:51
@shahkalpesh: thanks for the quick reply, but your suggestion would require the Recordset to be re-opened every time I upload a record right? If the table size is already fairly large and I have several hundred thousand records to upload this will slow the operation significantly ( I believe...). — Derek Cheng, Jul 26 '13 at 20:56
@DerekCheng: That you will have to write code, figure the time it takes with hundred thousand records. Looking at your code, here is how you can do it (note that it will have to be refined). `For i = 0 To n strsql = "SELECT * FROM table1 WHERE Field1 = " & Range("A" & i) rst.CursorLocation = adUseClient rst.Open strsql, strcon, adOpenStatic, adLockOptimistic If rst.RecordCount = 0 Then rst.AddNew Array("Field1", "Field2", "Field3"), Array(Range("A" & i), Range("B" & i), Range("C" & i)) rst.Update End If rst.Close Next` — shahkalpesh, Jul 26 '13 at 21:06
@HK1: The Err.Clear does not clear the key violation associated with the recordset. Additionally as shown in my example code, using "Resume Next" will allow the code to be ran without clearing the error.- MSDN:http://msdn.microsoft.com/en-us/library/aa266173%28v=vs.60%29.aspx — Derek Cheng, Jul 28 '13 at 18:34
Hi guys I found a solution to this problem its as simple as using the CancelUpdate method, but I think I will move to using Command going forward! — Derek Cheng, Jul 31 '13 at 21:18

Andy G · Answer 1 · 2013-07-26T20:22:40.100

I would not use error handling (handling ADO errors is tricky) but use Find to check each time that the key does not already exist, before performing the insert. If the key is an indexed field (as your description suggests) then you might consider Seek which is more efficient for a large dataset. (However, Seek does not work with a client cursor -adUseClient.)

Here is a code fragment that I found that outlines the steps:

If Not .EOF Then
   .MoveFirst
   .Find "TPItemNbr='" & m_TPItemNbr & "'", , adSearchForward
End If
If .EOF Then
   .AddNew
   !TPItemVendorID = m_TPItemVendorID
   !TPItemNbr = m_TPItemNbr
   !TPItemEUOM = m_TPItemEUOM
   !TPItemUOMFactor = m_TPItemUOMFactor
   !TPItemPUOM = m_TPItemPUOM
   !TPItemDescription = m_TPItemDescription
   !TPItemUnitCost = m_TPItemUnitCost
   !TPItemUnitLabor = m_TPItemUnitLabor
   .Update

That is, perform a Find each time within the loop, if EOF (end-of-file) is true then the key isn't already in the table, so an insert can be performed.

Added in response to further information about the compound key. I would create a Command object and use Execute. Yes, it requires creating a string, but you can trap and ignore errors for key violations.

The alternatives could be to

Run a separate SQL-statement to obtain a recordset of the list of duplicates
Loop through this storing the values in an array
Perform the AddNew each time checking the array

This seems messy to me, particular the aspect of searching the array.

Pursue the multi-find method linked in a comment.

Personally, I wouldn't use the Array() approach, I would just set the individual fields. IMO using Array adds some complexity and is more error-prone. But this is just my opinion ;) — Andy G, Jul 26 '13 at 19:38
First of all thank you for your answer. I've considered doing a search before the AddNew. On the performance side though if my table size consists of many rows could it slow down the procedure by a lot? — Derek Cheng, Jul 26 '13 at 19:50
Also if the table have a composite key (I know we shouldn't use composite keys...but its not up to me...) then the .Find would not work (correct me if i'm wrong here) and I will have to use this multifind function suggested by microsoft - http://support.microsoft.com/kb/195222 — Derek Cheng, Jul 26 '13 at 19:52

score 0 · Accepted Answer · answered Jul 31 '13 at 21:21

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

Handling AddNew Key Violations ADODB.Recordset

2 Answers2