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!!