Help!...Keeping two tables in a DB in sync with update, delete and append queries....

[ideasfactory]

Hi,

I need some help pretty quick......I have a Access 2010 DB with 2 tables, a linked table to Excel 'Excel_Schedule_TC5' and a linked Table to Sharepoint ' Sharepoint_Schedule_TC5'

The 'Excel_Schedule_TC5' data 900+ rows was imported into 'Sharepoint_Schedule_TC5' all good.

But now I need to keep both tables in sync ('Sharepoint_Schedule_TC5' does not change but 'Excel_Schedule_TC5' changes all the time adds, updates, deletes etc). I need to do the following:

1. Update query with inner join between both on UID field and update specific fields not all. (That is 10 fields from Excel_Schedule to Sharepoint_Schedule) - NEED Help with example SQL for points 2 & 3 below moreso.

I think I am okay using the following:

UPDATE Sharepoint_Schedule_TC5 INNER JOIN Excel_Schedule_TC5
ON Sharepoint_Schedule_TC5.[UID] = Excel_Schedule_TC5.[UID]
SET Sharepoint_Schedule_TC5.UID_Pred = Excel_Schedule_TC5.[UID_Pred];
....add other 9 fields.....etc

2. Delete query applied to 'Sharepoint_Schedule_TC5' with inner join between both based on UID, where records that have been removed from 'Excel_Schedule_TC5' but still exists in 'Sharepoint_Schedule_TC5' these records / rows need to be deleted, as I need to keep both tables in sync so both match the same records based on UID.

It would be appreciated if someone could share the SQL needed to achieve point 2.

3. Append / etc query to 'Sharepoint_Schedule_TC5' from 'Excel_Schedule_TC5' with inner join between both based in UID, where a record has been added in the 'Execl_Schedule_TC5' and does not exist in 'Sharepoint_Schedule_TC5' as I need to keep both tables in sync.

It would be appreciated if someone could share the SQL needed to achieve point 3 also.

I am relatively new to this so any help would be appreciated.

Thanks

 

[Kyle123]

Does Access have table Triggers?

 

[ideasfactory]

Hi no I want to run queries manually not triggered need the SQL for points 2 & 3

 

[ideasfactory]

The following SQL has been obtained but I think it is based on Oracle SQL which does not work in Access? can some help provide and turn the following into MS Access workable SQL please not sure if INNER join is needed or not?:

Update:

Update TableB
Set(Field1, Field2, Field3) = (select Field1, Field2, Field3 from TableA where TableA.UID = TableB.UID);


Delete:

Delete from TableB
Where not exists (select null from TableA where TableA.UID = TableB.UID);

Insert:

Insert into TableB
Select*
From TableA
Where not exists (select null from TableB where TableB.UID = TableA.UID);

 

[Joe4]

Cross-posted here: Update, delete and insert queries between two tables

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

 

[ideasfactory]

Hi,

Thanks for adding the link.....I was not aware of the cross-posting rule will follow going forward.

I would appreciated it if someone could help solve this post.

Cheers

 

[Joe4]

Your Update Query would look something like this:

UPDATE TableB
INNER JOIN TableA
ON TableA.UID = TableB.UID
SET TableB.Field1 = [TableA]![Field1],
TableB.Field2 = [TableA]![Field2],
TableB.Field3 = [TableA]![Field3];

Your Delete Query:

DELETE TableB.*
FROM TableB
LEFT JOIN TableA
ON TableB.UID = TableA.UID
WHERE TableA.UID Is Null;

Your Append (Insert) Query:

INSERT INTO TableB ( UID, Field1, Field2, Field3 )
SELECT TableA.UID, TableB.Field1, TableA.Field2, TableA.Field3
FROM TableA
LEFT JOIN TableB
ON TableA.UID = TableB.UID
WHERE TableB.UID Is Null;

 

[ideasfactory]

Hi,

The code works thanks VERY much.

I want to add all fields into TableB not individual ones please can you update below as I am not sure.

INSERT INTO TableB ( UID, Field1, Field2, Field3 )
SELECT TableA.UID, TableB.Field1, TableA.Field2, TableA.Field3
FROM TableA
LEFT JOIN TableB
ON TableA.UID = TableB.UID
WHERE TableB.UID Is Null;

As a final Query it would be good to do a comparison between TableA and TableB to see that all records and fields are the same, so only show a list of those UID's records that do not match. Hopefully with the three queries all fields will be the same but good to have a query check.

Thanks again

 

[ideasfactory]

Hi,

I think I have figured out the update query can you check below:

INSERT INTO TableB
SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.UID = TableB.UID
WHERE TableB.UID Is Null;

When I run the delete query below I get 'Could not delete from specific tables' error

DELETE TableB.*
FROM TableB
LEFT JOIN TableA
ON TableB.UID = TableA.UID
WHERE TableA.UID Is Null;

Thanks

 

[ideasfactory]

Hi,

Sorry when I run the insert query I get the attached error: Access can't append all the records to the table. Access set 1 fields(1) to Null due to a type conversion failure....

INSERT INTO TableBSELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.UID = TableB.UID
WHERE TableB.UID Is Null;

Any ideas? TableB is a linked Sharepoint table dont know if that makes any difference?