Can you change linked Excel file to Access path with Excel VBA

[raymond.allan]

Hi,

Using Excel 2000 and Access 2000

I have a few large Excel files linked to Access and use Excel to query
the required data

My program is now going global within the company I work with and it
is not guaranteeed that all users will have the same path names

I wrote an add-in that has full control where the files are saved and
the .mdb will always be in the same location along with my program and
other relevant files required.

Example, at work I have a network folder J:\Item Branch Metric, if I
work from home I manually use the Linked Table Manager in Access to
change this to C:\Item Branch Metric

Can VBA be used to do the above change ?

TIA for any help

 

[JLatham]

Yes, VBA can do that in both sides of the issue: within Excel and within
Access.

But for your global use, I'd suggest using a URL type path rather than a
path with a mapped drive. Use the path as displayed when you browse for the
file using My Network Places instead of the one shown when you browse for it
using My Computer. This also has the advantage of allowing the 'real world'
users to use the program without even having to have the share with the
database on it mapped to any drive at all.