Update data from one table to another and insert if new ms access .net

Question

i have 2 tables 'news' and 'news_dev' each has a isapproved tick-box

i am trying to copy the data from 'news_dev' into news but only if they have a tick and also if its a new record that is ticked add the new record to the 'news' table

my first method was this

Sub NewsBtn_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("databasehere") & "")
    AccessConn.Open()
    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("DELETE * FROM news", AccessConn)
    AccessCommand.ExecuteNonQuery()
    Dim AccessCommand2 As New System.Data.OleDb.OleDbCommand("INSERT INTO news SELECT * FROM news_dev WHERE isapproved", AccessConn)
    AccessCommand2.ExecuteNonQuery()
    sucsessLabel.Visible = true
    sucsessLabel.Text = "News Updated"
    AccessConn.Close()
End Sub

Now this worked BUT if you untick an item in 'news_dev' it removes it from 'news' which is not what i want.

So basically im trying to update one table from anther based on a tick and if a record is ticked in one and not in the other table then add it.

Hope all this makes sense cos my head it fried but any help would be most appreciated.

Cheers Andy

score 0 · Accepted Answer · answered Nov 26 '13 at 18:51

0

It seems to me that your problem stems from the first query that you run. You are deleting all records from the news table and then in the second query, you are inserting only the records that are approved.
If you have a primary key field that is not an autonumber, you could skip the first query and use this code for the second query. This example inserts the records that are approved and don't exist in the new table.

"INSERT INTO news SELECT * FROM news_dev WHERE isapproved AND PRIMARY_KEY NOT IN 
(SELECT PRIMARY_KEY FROM news)"

answered Nov 26 '13 at 18:51

HelloW

1,587
2
13
24

hey mate been playing with this code and its not quite working as i thought. if i add a new record to news_dev and publish it using your query it works fine but when i update a record in news_dev publish it using your query it does not update the news table any ideas mate ? – Andy Dyas Nov 27 '13 at 14:28
Sorry I took so long to respond. This is because the record already exists in the table. This code only checks for new records not modified ones. I think it would be best to ask another question since it seems that it has turned a corner. – HelloW Dec 04 '13 at 02:32

Update data from one table to another and insert if new ms access .net

1 Answers1

Linked