Addnew does not add row when similar row there

Question

I have a problem that adding ( overwriting existing 123 key) does not work, how to force updating a row if conflict occurs?

db.OpenRecordset("table1", dbOpenTable)
 .AddNew ' create a new record
 .Fields("key") = "123"
.Update ' stores the new record

code works when DB does not have key 123 yet

Answer 1

If you're using a DataTable, you want to use the LoadDataRow method, which either adds a new row or updates an existing row. I don't know if this method is present in a Recordset.

Answer 2

You've got this tagged VBA and ado.net. But ADO.Net is not available to VBA. Could you provide clarification?

Assuming you are in VBA:

Public Sub Example()
Dim db As DAO.Database
Set db = Access.CurrentDb
With db.OpenRecordset("table1", dbOpenDynaset)
    .FindFirst "Key=123"
    If .NoMatch Then
        .AddNew ' create a new record
        .Fields("key") = "123"
        .Update ' stores the new record
    Else
        .Edit
        .Fields("myfield") = "foo"
        .Update
    End If
    .Close
End With
db.Close

End Sub