find the duplicate and write it in log file.

Answer 1

More of a strategy then a specific answer but ...

When importing data from external sources we'll often insert the data into staging tables that do not have the same keys/contraints placed on them and then sanitize the data prior to insertion.

What is done during "sanitation" depends on your requirements (for example, when you have two of the same account numbers are the records the same or are the data fields different, if the fields are different, how do you choose which data to use?). And then insert/move it into the production table once sanitization is complete.

Answer 2

I ran into this problem and what I did is to make a collection that I stored the object and the key into the Key. If I try to add a duplicated key I get an error.

This is the easyest way I found to do this in vb6. in c# is dictionary.

Answer 3

My suggestion would be to add error handling to CommitNew to see if the row inserted would create a primary key violation, and if so then perform other handling.

Example:

Sub CommitNew()
  ''#Add records to DneFrc table 
  On Error GoTo CommitNew_Error
  With rcddnefrc
    .Requery
    .AddNew
    .Fields![RTN] = rcdDNE.Fields![RTN]
    .Fields![AccountNbr] = rcdDNE.Fields![AccountNbr]
    .Fields![FirstName] = rcdDNE.Fields![FirstName]
    .Fields![MiddleName] = rcdDNE.Fields![MiddleName]
    .Fields![LastName] = rcdDNE.Fields![LastName]
    .Fields![Amount] = rcdDNE.Fields![Amount]
    .Update
  End With
  Exit Sub   ''# If no error, exit routine.
CommitNew_Error:
  If Err.Number = -2147217873 Then
    ''# code here will only execute if the constraint violation occurs
    Call WriteDuplicateAccountToFile()
    Err.Clear()       ''# This clears the error, since you handled it
  Else
    ''# Do stuff with other errors.
    ''# If you're not sure, at least display what error its giving, like so
    MsgBox "The following error was encountered when new record was saved:" & _
      vbNewLine & CStr(Err.Number) & " - " & Err.Description & vbNewLine & _
      "New record not saved.", vbOkOnly + vbCritical, "Error"
  End If
End Sub