I am trying to merge two tables and update one table with the merged records from two separate MS access databases
Both tables are identical and both tables are password protected with auto number primary key
The following code merges the datasets memory, or at least by the msgbox messages the record count is correct.
But it does not update to the table in the access database..
I have searched the net long and hard and am really at a loose end..
daDBTarget = New OleDbDataAdapter
daDBTarget.SelectCommand = New OleDbCommand("SELECT * FROM DBUSERTASK", Conn1)
daDBSource = New OleDbDataAdapter
daDBSource.SelectCommand = New OleDbCommand("SELECT * FROM DBUSERTASK", conn2)
MsgBox("Dataset 1 dstTaskComp is full with " & dsDBTarget.Tables(0).Rows.Count & vbCr & vbCr & "Dataset 2 dstTaskComp is full with " & dsDBSource.Tables(0).Rows.Count)
MsgBox("Dataset 1 dstTaskComp is now merged and full with " & dsDBTarget.Tables(0).Rows.Count)
Dim cb As New OleDbCommandBuilder(daDBTarget)
There are a couple of details I dont know. For instance, if the source/master has deleted rows which are not (yet) deleted in the destination, should they be deleted as part of the merge? Since they do not exist in the Source, nothing will happen to them in the process unless you handle it manually. I ignored that aspect since it was not mentioned.
The key element to know is that each row has a
RowState indicating -- what if anything -- the
DataAdapter will do with each row. There are some key steps elements wrong or missing:
DataAdapterfills a table, it sets all the row states to Unchanged. In order for the source rows to be eligible for insert/update to the Dest, you'd want them to have a
PreserveChanges = Truewhich tells OleDB to ignore column level changes in the source.
Not related, but important is to dispose of DB Provider objects when you are done with them. This will merge 2 tables from the same DB, so the same connection can be used, but otherwise illustrates the process. I also skipped the
DataSets as they are not essential.
Dim SQLa = "SELECT * FROM MergeA" Dim SQLb = "SELECT * FROM MergeB" Dim dtSrc As New DataTable Dim dtDest As New DataTable Using dbcon As New OleDbConnection(ACEConnStr) Using da As New OleDbDataAdapter(SQLa, dbcon) dbcon.Open() ' Do Not set the RowState to UnChanged, please da.AcceptChangesDuringFill = False da.Fill(dtSrc) End Using Using da As New OleDbDataAdapter(SQLb, dbcon) Dim cb = New OleDbCommandBuilder(da) ' same connection, already open ' load the schema to get the PK etc da.FillSchema(dtDest, SchemaType.Source) da.Fill(dtDest) ' merge the tables dtDest.Merge(dtSrc, False, MissingSchemaAction.Add) Dim rows = da.Update(dtDest) End Using ' close and dispose End Using
Perhaps more important is that item
2 - Uniform exists in the Destination but had been deleted in the Source. The
DataAdapter wont remove it because it is there and the code didnt change the
Deleted. Merging will accumulate rows and column data changes depending on the options used, but removing deleted rows is more like a synch operation. It is pretty simple to incorporate though.