VBA Code to overwrite a saved path

[Glen]

Good morning. I am not sure if this is possible and I am not having
much luck with keyword searches for the answer. Is it possible to
write VBA code that will overwrite a path name that is used for linking
tables?

The big picture is this, I have a DB that uses linked tables from a
back-end DB. The tables are used for lookup data mostly. This
back-end DB will be placed on different servers for different
workstations and I want to include code that will allow the user to
locate the back-end for initialization. I don't want the user to have
to locate the back-end every time they enter the DB though. So, can I
have a string variable that will save this path from the first time
they find the back-end. If this is possible, at start-up I can use the
path name to check for verification of the tables and link them and, if
it is not found, I will provide a pop-up that will ask the user to find
the back-end again. When they point to the back end again, I can
overwrite the pathname to this value for the next time they start up.
I hope this is clear - Thanks for any help you can provide.

 

[Douglas J Steele]

What you're describing is a fairly common technique.

I don't have a complete example I can point you to, but
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web" shows how
to change the linkages programmatically, and will even prompt the user to
supply a new backend if required (make sure you follow the link from that
page to http://www.mvps.org/access/api/api0001.htm to get all of the code
you need). All you need to do is add some logic that checks whether the
Connect property is what you want before you ask the user if they want to
change the back-end.

 

[Glen]

Thank you very much Doug, for pointing me in the right direction.