Background information:
I have a DataGridView, I can load a table from MS Access into it and/or also add data. It is not databound (and I don't want t). What I did previously was to delete the data from MS Access table before saving new data. But I would like to do it the proper way (+ there will be less chance of losing data).
Issue: When I do
Adapter.Update(DataTable) 'OleDb.OleDbDataAdapter
the data simply gets added (inserted) at the end, like when you add rows to datagridview.
What I want to do is overwrite the data in MS Access table, so that it looks identically to newly saved data (rows that can be updated get updated, new ones added, removed ones deleted)
In other words, if I'm saving 6 rows, I want to see 6 rows in database, not more not less.
I also tried loading the data, then changing it, and saving it back, but the result is the same. Eg if I load 5 rows, and save 5 rows, I end up with 10 rows in it after that save.
Adapter = New OleDb.OleDbDataAdapter("SELECT * FROM " & DB_TableName, DB_Connection)
Dim TempDataTable As New DataTable
BotDB_Adapter.Fill(TempDataTable )
'edit data here
Adapter.Update(TempDataTable)
TLDR : How do I save a DataTable to MS Access table so that it overwrites data already in it.
It was due to row state being "added", the only way to fix it is altering individual row states to proper states.
I do not see other way to solve this so I'll close it as there aren't any better answers.