Relative file path

[mino'aka]

I have two related problems:
1. I have an Access file (A.mdb) which is linked to an Excel file (E.xls).
The link was created in A.mdb by File-Get External Data-Link Tables. Both
files
are in the same directory.
The problem is that when I move the two files A.mdb and E.xls to another
computer (e.g., office to home) nothing works because the links refer to the
path and directory structure on the original computer, which is different.
The question is: how can I make the links (from A.mdb to E.xls) relative such
that the links will refer to current directory (as opposed to a specific
file
path) so that the two files can be ported to different computers.

2. For the same two files A.mdb and E.xls, created as described above:
In E.xls I have created queries by Data-Import External Data-New Database
query
(where the database is in A.mdb).
The problem is the same as above: when I move the two files A.mdb and E.xls to
another computer the queries do not work because the links refer to the path
and directory structure on the original computer, which is different.
The question is: how can I make the queries relative such that the links
will
refer to current directory (as opposed to a specific file path) so that the
two files can be ported to different computers.
Thank you.

 

[Allen Browne]

You cannot use linked paths.

You may be able to solve the problem by using the Linked Table Manager
(add-in). Or you can programmatically loop through the TableDefs and set the
Connect property of each, remembering to RefreshLinks.

 

[mino'aka]

Thanks. We're working on this, but I have another related question about
moving these files to various computers. What are the implications if we just
move the files under the exact same file path off of the C: drive on both
computers? Will we need to re-create the queries and file paths? We're not
networking this thing, we're just trying to do some research work at home and
at the office. (We're at a university)

 

[Allen Browne]

If the path is unchanged, there is nothing to do. It just works.

If the path does change, you need to loop through your TableDefs, and change
the Connect property, remembering to RefreshLink.

Alternatively, you may be able to use the subst command (at the Windows
command prompt) to simulate a drive/path you don't have locally.