OLEDBCommandBuilder and moving data accross tables

So, here it goes:

I need to copy data from table in Access database, in another table from another Access database.

Column names from tables are the same, except the fact that the FROM table has 5 columns, the TO table has 6.

here is my code:

dsFrom.Clear()
dsTO.Clear()
daFrom = Nothing
daTO = Nothing
conn_string1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="etc.mdb;"
conn_string2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="database.mdb;"
query1 = "Select * from nomenclator_produse"
query2 = "Select * from nomenclator_produse"
Conn1 = New OleDbConnection(conn_string1)
conn2 = New OleDbConnection(conn_string2)
Conn1.Open()
conn2.Open()
daFrom = New OleDbDataAdapter(query1, Conn1)
daTO = New OleDbDataAdapter(query2, conn2)
daFrom.AcceptChangesDuringFill = False
dsFrom.HasChanges()
daFrom.Fill(dsFrom, "nomenclator_Produse")
dsFrom.HasChanges()
Dim cb = New OleDbCommandBuilder(daFrom)
dsTO = dsFrom.Copy
daTO.UpdateCommand = cb.GetUpdateCommand
daTO.InsertCommand = cb.GetInsertCommand
daTO.Update(dsTO, "nomenclator_produse")

Because the FROM table has 5 rows and the other has 6, I'm trying to use the InsertComman开发者_如何转开发d generated by the DataAdapter of the first table.

It works, only that it inserts the data from the FROMTABLE in the same FROMTABLE, instead of TOTABLE.

---

I think you should execute a query on the lines of:

    conn_string2 = ";Database=c:\Docs\DBFrom.mdb;"
    query1 = "INSERT INTO nomenclator_produse (field1) " _
    & "SELECT f.Field1 from [" & conn_string2 & "].nomenclator_produse f " _
    & "LEFT JOIN nomenclator_produse t " _
    & "ON f.id=t.id WHERE t.id Is Null"