Copy from ORACLE to ACCESS ?

[Golf]

The code below copy SQL result from ORACLE database to ACCESS database
at the first time but other times duplicate data , when i assign key to the Target table , the compiler give a error message

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again "

Dim cnOra As OracleConnection = New OracleConnection("Data Source=prod;User Id=xxx;Password=xxx;")
Dim cnAccess As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\xxx1.mdb;User Id=admin;Password=;")

        Dim da1 As OracleDataAdapter, dt As DataTable, cmd As OleDbCommandBuilder
        Dim da2 As OleDbDataAdapter
        da1 = New OracleDataAdapter("Select  * FRom abc", cnOra)
        da1.AcceptChangesDuringFill = False
        dt = New DataTable
        da1.Fill(dt)
        da1.Dispose()

        If Not dt Is Nothing Then
            da2 = New OleDbDataAdapter("SELECT * FROM xyz", cnAccess)
            cmd = New OleDbCommandBuilder(da2)
            da2.InsertCommand = cmd.GetInsertCommand
            da2.Update(dt)
            da2.Dispose()
            cmd.Dispose()
            dt.Dispose()
        End If

 

[cjard]

Er, did you have a question?

 

[Golf]

Ok.. i want to transfer the data from Oracle to Access OVERWRITE the old data not Duplicate

 

[cjard]

Delete the old data then!

Either that or iterate over the rows yourself, performing a SELECT COUNT(*) FROM access_table and setting the added or modified as necessary, the the dataadapter will use INSERT or UPDATE as necessary:

        If Not dt Is Nothing Then
            da2 = New OleDbDataAdapter("SELECT * FROM xyz", cnAccess)
            OleDbCommand cmd = new OleDbCommand("SELECT COUNT(*) FROM xyz WHERE primaryKey = ?", cnAccess)
            cmd.Parameters.AddWithValue("pk", "dummy")
            cmd = New OleDbCommandBuilder(da2)
            da2.InsertCommand = cmd.GetInsertCommand

ForEach dr as DataRow in dt.Rows
  cmd.Parameters("pk") = dr("priKeyColInDt")
  If cmd.ExecuteScalar() = 1 Then 'row exists
    dr.SetModified()
  Else
    dr.SetAdded()
  EndIf
Next dr


            da2.Update(dt)
            da2.Dispose()
            cmd.Dispose()
            dt.Dispose()
        End If

 

[Golf]

Thanx a lot i will try your solution