VBA to change Linked Tables for Test and Production

Jerry Paladino
Jerry Paladino used Ask the Experts™
on
I have an Access 2007 db with 13 linked Excel files on a network share.   I would like some VBA to change the links to a local directory on my PC for testing and then back again later.  I do not have experience with Access VBA but I do have intermediate skills with VBA in Excel so I will probably be able to follow the Access VBA just not write it from scratch.   I can put the Test and Prod directories names in a Table or hard code them in the VBA.  Does not matter... Just looking for a hands off way to change them back and forth.

Thanks,
Jerry
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Trungk43,
Your solution looks good but a bit too complicated for me to pull apart and figure out what pieces I need.  I'm sorry, I just don't know the Access object model.  Your solution assumes that all file/dbs are in the same location.  The Excel files "Linked" to my Db are scattered across 4 different subdirectories.  If I could enter them in a table with a field that indicated they were Test or Prod (like the graphic below) could you help me with something simple that would link all the files based on passing "Test" or "Prod" to the VBA routine?
 

File-List.png
Commented:
I have one FrontEnd with 2 excel file.

You must update the table tbl_tableManifest first. Then open frm_ChangeBackEnd form.

Good luck!
FrontEnd.mdb
TestLink.xls
TestLink2.xls
trungk43,
This works perfect for what I need.  Thank you very much for simplifying it for me. This will save me hours linking table across the VPN to the network servers.

Jerry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial