Update a table from other table when there is a change

[mansied]

Hello
I have two tables with the same structure and data .
I want to have a query to update only records from table2 from table 1 where changed .
both tables have the same fields

 

[June7]

Reviw https://stackoverflow.com/questions/6199417/upserting-in-ms-access

 

[mansied]

Reviw https://stackoverflow.com/questions/6199417/upserting-in-ms-access

Thank you , I did this update to update table 2 from table 1 when there is a change in fields
but I need just update records was changed .
I added inner join and <> in criteria but ,...

 

[June7]

Well, if you don't want new records then just do an UPDATE sql. If you don't know which field(s) or record(s) will have change, update all of them.

 

[plog]

That's not how databases are to work. Having 2 tables with the same structure is wrong; synching data between tables is wrong.

The purpose of a database is to not have duplicate data and data dependent on other data all over the place. Can you explain why you have 2 tables with the same structure and what the difference is? Also, why must this data be synched?

 

[mansied]

Well, if you don't want new records then just do an UPDATE sql. If you don't know which field(s) or record(s) will have change, update all of them.

I don't know which records ..
I have a update date trigger to see which records has been updated
If I update all records ,Update date doesn't make sense any more ..

 

[June7]

True, need some criteria to restrict the UPDATE.

Agree with Plog. Why are you doing this?

 

[mansied]

True, need some criteria to restrict the UPDATE.

Agree with Plog. Why are you doing this?

I have a table in access is the same as table in oracle .
We update oracle tables with this table in access . We want to update oracle table with updated records in access ,not all records be updated . Just those has been changed .

 

[arnelgp]

can you not just used the Linked Oracle table to your form, instead of maintaining a Local table?
if there are two/three persons working on same table and same record at same time, you'll get problem there.

 

[mansied]

can you not just used the Linked Oracle table to your form, instead of maintaining a Local table?
if there are two/three persons working on same table and same record at same time, you'll get problem there.

Yes access table is populating by almost 60 persons ,.then at the end of month,we transfere it to oracle table for reporting on powerbi.
So I have no choice ...

 

[arnelgp]

you can add another Field to your Local table (Changed, yes/No field).
while on Design view of your table, you add Data Macro(Create Data Macro->Before Change):

create a Select Query on your Local table to Filter only records whose [Changed] field is set to True (query1):

select * from LocalTable where [Changed]=True;

Create another Query that will Join LinkedOracleTable to Query1 and update your Oracle table:

Update linkedOracleTable As X Inner Join Query1 As Y
On X.PKFieldName = Y.PkFieldName
Set
X.Field1 = Y.Field1,
X.Field2 = Y.Field2,
[X.Field3 = Y.Field3],
...
...

 

[June7]

Then afterwards need to UPDATE Changed field to False.

 

[Pat Hartman]

We should discuss why you are duplicating the data AND trying to keep the two tables in sync. This is really poor practice as the others have mentioned.

 

[GPGeorge]

Yes access table is populating by almost 60 persons ,.then at the end of month,we transfere it to oracle table for reporting on powerbi.
So I have no choice ...

This is somewhat backwards from the experience I had working with Oracle tables, but the situation is similar. We were pulling data from an Oracle data warehouse into local Access tables for weekly and monthly reports. You're pushing "warehouse" data into the Oracle table.

I do suspect that the Access data isn't available to your PowerBI report. Correct?

As suggested, linking Access to the Oracle table is an essential first step. From there, though, I'd approach the next step a little differently. Instead of a flag field for "UpdateData", I'd invest time in queries using left outer joins to identify NEW records that must be appended to the Oracle table,

E.g. "SELECT Field1, Field2 FROM AccessLocalTable LEFT OUTER JOIN OracleRemoteTable ON AccessLocalTable.PrimaryKey = OracleRemoteTable.PrimaryHey WHERE OracleRemoteTable.PrimaryKeyField Is Null;"

Turn that into an Append query to do the first step, i.e. add new records. I'll leave that to you to work out....

Then, to update the corresponding records in the OracleRemoteTable with changes made in the last work period to the AccessLocalTable records, create an Update query:

e.g. "UPDATE OracleRemoteTable INNER JOIN AccessLocalTable ON AccessLocalTable.PrimaryKey = OracleRemoteTable.PrimaryKey
SET Field1 = AccessLocalTable.Field1, Field2 = AccessLocalTable.Field2;"

That will ensure that ALL values in ALL fields in ALL records are the same in the Oracle table as they are at that moment in the Access table. That is fine logically if you want to synch them entirely. It may be time consuming, though, so you could apply criteria to restrict the records.

e.g. "UPDATE OracleRemoteTable INNER JOIN AccessLocalTable ON AccessLocalTable.PrimaryKey = OracleRemoteTable.PrimaryKey
SET Field1 = AccessLocalTable.Field1, Field2 = AccessLocalTable.Field2
WHERE AccessLocalTable.Field1 <> OracleRemoteTable.Field1 OR AccessLocalTable.Field2 <> OracleRemoteTable.Field2; "

If you really want to fine tune it, you could create a series of Update queries aimed only at single fields, and run them in transaction in a VBA function.