access 2013 vba relink back end tables if not found

sumhungl0

Board Regular
Joined
Jan 1, 2014
Messages
119
good afternoon,
I am looking for a way of relinking back end tables if the links get broken. back end file name changes sometimes. my links are not just a back end single accdb, there are 3 accdb's and 2 excel xlsx or xlsm files. I used access to link them, however when updated back ends (accdb, xlsx, xlsm) are put in my shared folder, then I hafta manually relink the data correctly. other users rarely complete that process correctly.

is there a macro/vba way to check my links and then open the file dialog to allow the user to select the correct back end link for each link?

a little about how this db is used... the front end is pasted to several user's machines. I have one back end accdb that stores data collected by myself and users. I have a few other back end accdb, xlsx, xlsm that the front end uses as read only data in the form of linked tables. new versions of these reports come out every few days/weeks, so I put them in the shared folder for the front ends to use. these reports come to me in these different formats (accdb, xlsx, xlsm). the front ends use these updated reports/backend links read only in queries and reports while any data that is input by users will go into tables in my single back end.

any help is greatly appreciated.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,217
there's no way for Access to know what the backend was renamed to.
It must be done manually.
 
Upvote 0

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,217
these backend files should not be getting renamed. The should STAY a constant name.
If you are using files with changing datestamps in the filename, you should start using a constant name....ie: DeptsToImport.xlsx, or Data2Import.xlsx.

All new files will be saved as their dateStamp name AND to the constant name: Data2Import.xlsx.
now, No files get renamed,
no relinking is needed.
 
Upvote 0

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
If you can identify the "back end" files with at least some kind of pattern (or if they are the only files in the shared folder) then you can relink them automatically (you wouldn't even need a dialog box).

Or (as noted above) you could rename them.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,186,692
Messages
5,959,208
Members
438,404
Latest member
RailEngineer76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top