Update Access Tables In Multiple Databases Using Inner Join SQL Statements

Here's an example of how to update one MS Access table from another table in ANOTHER database using an INNER JOIN SQL statement.

You might need to use this to restore single fields from one database to another, perhaps to undo changes made to one field from a backup copy of your database. Maybe your customer updated all the prices of all their products by mistake? :o)

Anyway, here's example syntax to restore selected prices from one Access database from a backup copy:

UPDATE [c:\data\data.mdb].stock as stockLive 
INNER JOIN [c:\data\dataBackup.mdb].stock as stockBackup
ON stockLive.stock_code = stockBackup.stock_code
SET stockLive.price = stockBackup.price
WHERE stockLive.Sales_Nominal_Code IN ('160', '180', '190', '192');

Hope that's helped you, I can never remember the exact syntax without having to look it up...
Good luck.

Page Updated 04/07/11