Full Update of one table based on another table need up to 3 steps:
1. A delete query to remove records not present in source table from target table;
2. An update query to update all remaining records in target table with info from matching records in source table;
3. An insert query to add into target table records present in source table and missing in target table.
Or you simply clear all info from target table, and insert it anew from source table. In case you have backend database in SQL server, it will something like
Code:
DELETE * FROM TargetTable
INSERT INTO TargetTable
SELECT * FROM SourceTable
NB! As you link the Excel table into FrontEnd, the easiest way to run the insert query will be in front-end too. You update SQL Server database table linked into front-end with new data.
NB! When your database is multi-user one, you may have locking problems when you want to update the whole table in back-end, and there are several users using the app at same time.
You can also update the table in SQL Server running a stored procedure in SQL server. The stored procedure reads data directly from Excel table, and updates the info in SQL Server Database table with it. You can call the stored procedure from your Access front-end using a pass-throw query, or you can set up a job, which runs the procedure on schedule.