CODEWITHSUNDEEP
sqlms-accessvba
Alex Gordon
Alex Gordon

Reputation: 60691

solution for updating table based on data from another table

i have 2 tables in access

this is what i need:

1. if the PK from table1 exists in table2, then delete the entire record with that PK from table2 and add the entire record from table1 into table2
2. if the PK does not exist then add the record

i need help with both the sql statement and the VBA

i guess the VBA should be a loop, going through every record in table1. inside the loop i should have the select statement

Upvotes: 0

Views: 234

Answers (3)

HansUp
HansUp

Reputation: 97101

I don't think you need a VBA loop, just two SQL statements.

First delete the matching rows from table2.

DELETE 
FROM table2 AS m
WHERE pk IN (SELECT pk FROM table1);

Then append all the rows from table1 into table2.

INSERT INTO table2 (
    pk,
    field2,
    field3,
    field4)
SELECT
    i.pk,
    i.field2,
    i.field3,
    i.field4
FROM
    table1 AS i;

Upvotes: 1

VeeArr
VeeArr

Reputation: 6178

DELETE FROM table2
  WHERE EXISTS
  (SELECT * FROM table1, table2
   WHERE table1.pk=table2.pk);

INSERT INTO table2
SELECT * FROM table1;

This assumes table1 and table2 have the same columns.

Upvotes: 1

Thomas
Thomas

Reputation: 64635

I would do this in two statements. One that deletes the proper rows and another to insert the row.

Dim oDB As DAO.Database
Dim sSQL As String
Dim oQry As DAO.QueryDef

Set oDB = DBEngine.Workspaces(0).Databases(0)
sSQL = "Delete From Table2 Where Exists( Select 1 From Table1 Where Table1.Id = Table2.Id )"
oDB.Execute sSQL, dbFailOnError

sSQL = "PARAMETERS [Col1Param] Text, [Col2Param] Text, [Col2Param] Text; " & _
    "Insert Into Table1(Col1, Col2, Col3) Values([Col1Param], [Col2Param], [Col3Param])"
Set oQry = oDB.CreateQueryDef("", sSQL)
oQry!Col1Param = "Col1Value"
oQry!Col2Param = "Col2Value"
oQry!Col3Param = "Col3Value"
oQry.Execute, dbFailOnError

oQry.Close

Set oQry = Nothing
Set oDB = Nothing

Upvotes: 1

Related Questions