Redirect multiple Linked Tables using VBA

[Wiggles8831]

I have 25 linked tables linked to text files in MS Access. I would like to make a VBA module that will redirect/relink all the tables to a different file extension. Below is a sample of what the linked tables' paths currently look like (there are 25):
C:\External\Company1\Submissions\20120103
C:\External\Company2\Submissions\20120103
C:\External\Company3\Submissions\20120103
C:\External\Company4\Submissions\20120103
C:\External\Company5\Submissions\20120103

The list of all 25 companies resides in a table called CompanyList.


What I would like to happen when I run the module is to have a window pop up where I can edit the final portion of the file extension, e.g. change \20120103 to \20120110 for all 25 linked tables.
I'm thinking something like "C:\External\" + [COMPANYLIST] + \ [input from pop up window].
I use basic SQL coding all the time, but I'm definitely just a beginner at VBA, so any help would be greatly appreciated!
Thank you!

[June7]

Code to programmatically modify table links in this thread http://forums.aspfree.com/microsoft-...ue-323364.html

[Wiggles8831]

Thanks for the pointer June, definitely a start.

My big issue is that all my files aren't stored in the same file directory.

C:\External\Company1\Submissions\20120103
C:\External\Company2\Submissions\20120103
C:\External\Company3\Submissions\20120103

Is there a way that I can update everything to the left and to the right of \CompanyX\ and use a table to fill in all 25 different values for the company?

[Wiggles8831]

Nevermind, I figured it out. Thanks!

[June7]

Yes, you could open a recordset of the table and loop through the recordset and concatenate the field into the Old and New path strings. I am guessing that was your solution.