sqlvb.netms-access-2007copy-pasteinsert-into

How to copy records from one table to another database table with different connection string?


I have two(2) MS Access databases with different connections in different location. Deleting records from the table of the first database is working but inserting new records that was copied from the table of the second database is not working. The table of the first database remains blank. What happened was it was inserted the new records to the table of the second database, that's why the second database have many records because it copied and inserted to itself. What I want is that the copied records from second database will be pasted/inserted to the first database.

SQLStr1 is the location string of the first database SQLStr2 is the location string of the second databas

Dim conn As OleDbConnection
    Dim conn2 As OleDbConnection

    Dim cmd As OleDbCommand
    Dim cmd2 As OleDbCommand
    Dim SQLStr1 As String
    Dim SQLStr2 As String

    conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=psipop.accdb;Jet OLEDB:Database Password=cscfo13poppsi;")
   'I used 'psipop' because the database is located the same with the application.
    SQLStr1 = "DELETE * FROM pop 'psipop'"
    conn.Open()

    cmd = New OleDbCommand(SQLStr1, conn)
    cmd.ExecuteNonQuery()

    'I used " & TextBox3.Text & " because the textbox3 contains the path of the another database.
    conn2 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" & TextBox3.Text & "' ;Jet OLEDB:Database Password=cscfo13poppsi; ")

    SQLStr2 = "INSERT INTO pop SELECT * FROM pop IN '" & TextBox3.Text & "'"
    conn2.Open()
    cmd2 = New OleDbCommand(SQLStr2, conn2)
    cmd2.ExecuteNonQuery()

Please help me to this. Thank you.


Solution

  • E.g.

    Using sourceConnection As New OleDbConnection("source connection string here"),
          destinationConnection As New OleDbConnection("source connection string here"),
          insertCommand As New OleDbCommand("INSERT statement here", destinationConnection),
          adapter As New OleDbDataAdapter("SELECT statement here", sourceConnection) With {.InsertCommand = insertCommand,
                                                                                           .AcceptChangesDuringFill = False}
        Dim table As New DataTable
    
        adapter.Fill(table)
        adapter.Update(table)
    End Using
    

    That will populate the DataTable from one database and insert the rows into the other. You'll need to configure the insertCommand with appropriate parameters. Take special note of the AcceptChangesDuringFill property, which ensures that all DataRows have a RowState of Added and are therefore ready to be inserted. Without that, all RowState properties will be Unchanged and nothing will get saved.