Problem using OLEDBCOMMANDBUILDER.

Question

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 InsertCommand 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. :|

please help me :(

Answer 1

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"