Update, delete and insert queries between two tables

[ideasfactory]

Hi,

I have two tables in a Access 2010 DB as follows:

TableA (linked to Excel) and TableB (linked to sharepoint list)

TableA gets records added, updated and deleted regularly and TableB feeds sharepoint list so remains static there is not link between them.

An initial load from TableA to TableB was performed however TableA has had many updates, inserts and deletes and I need three queries update, delete and insert which I will manually kick off to align both tables.

The field that is in both table and ties tables together is UID.

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);

[ItsMe]

You can get an idea of what SQL looks like within Access by using the intrinsic tools within the Query Builder. You can create an action query by using the buttons at the top left of the ribbon. For instance, create an update query using the "Update" button. Afterwards, you can view the SQL by selecting the "SQL" option under the View button located further to the left.

In order to manage deleting the unmatched records, you will need to retrieve data that is unmatched between the tables before deleting. I suggest using the Query Wizard to create a new Query Object and select Find Unmatched Records.

By joining fields, typically fields that are not PK's, you add Is Null criteria to one of the sides.
Here is a sample SQL. Keep in mind the Is Null will be the record you will retrieve. You want to retrieve the record that does not have a match, the record that is null when considering the JOIN.
...

SELECT aqryModified.Web_Address
FROM aqryModified LEFT JOIN TableB ON aqryModified.aDomainDot = TableB.DomainDot
WHERE (((TableB.DomainDot) Is Null));

...

When appending data to a table, the SQL is rather simple. You can use the intrinsic tools to help you create SQL that will look similar to this.
INSERT INTO tblB (MyField)
SELECT tblA.MyField
FROM tblA

[CJ_London]

if you are making table B the same as table A, why not just delete all the records in table B and copy Table A across

[ideasfactory]

if you are making table B the same as table A, why not just delete all the records in table B and copy Table A across

Hi.......thanks for the info I will read the post above after this.

TableB is linked to Sharepoint if I delete all from TableB and then copy to TableA then the data will be deleted from Sharepoint which then recreates records in the _ID field which I do not want so need update, delete and insert from TableA to TableB

[ideasfactory]

Hi,

Thanks for the info.......I am new to access etc so not really sure how to use the wizard etc. Can you provide the SQL using the TableA and TableB construct for update, delete and insert.

Really appreciate your help.

Cheers

[ItsMe]

No, sorry. I will not have time to click on wizard buttons for you. It is very easy and it is how you can help yourself to learn Access. I suggest you give it a try. If you fail, you may have new questions that a help forum might be able to answer.