Help! Update Linked tables to new path

[Ndain]

I have developed an excel tool that is used to do some complex calculations. Once the calculations are complete, I use an Access DB to query the records up to a SharePoint List. The Excel tables and SP lists are all linked tables in Access.



I also have about 10 users using their own version of this tool at the same time. So that's 10 excel files and 10 Access DBs. When I make updates, I like to copy one acess file and paste it in the other 9 folders to replace the existing file. This breaks the links and I have to manually relink/refresh the linked excel/SP tables.

I would like to create an Access Macro that I can run that would update these linkes automatically. Keep in mind that when I overwrite all the Access files the tables are linked to C:\folderA\excelfile.xlsm but I want them to be linked to the excel file of the same name within the same folder as the access database 9C:\folder\excelfile.xlsm.

Any help would be appreciated!!

[ranman256]

if you link in the file, c:\myfile.xls
then get a new one, the overwrite the old one using the exact same name,
then no links are broken. The new data has the same name as the old one, so links remain.

[Ndain]

if you link in the file, c:\myfile.xls
then get a new one, the overwrite the old one using the exact same name,
then no links are broken. The new data has the same name as the old one, so links remain.

The path of the excel file is changing. Same structure but different location. I want the DB to find the new file within the same path as itself and re link.