Link to home
Start Free TrialLog in
Avatar of Jerry Paladino
Jerry PaladinoFlag for United States of America

asked on

VBA to change Linked Tables for Test and Production

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
Avatar of trungk43
trungk43
Flag of Viet Nam image

Avatar of Jerry Paladino

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of trungk43
trungk43
Flag of Viet Nam image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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