Setting a relative path for a linked Excel File

[adrian]

Hi,

I am linking an Excel file to my MS Access database. As the pair of files
(1 Excel File linked to an Access file) get circulated around my colleagues,
we face an annoying problem i.e. the MS Access file keeps losing track of
the linked Excel file whenever it got transferred to a new environment and
we got to reset the path again. For example in Computer A: The link is
C:\ABC\XYZ.xls. However, this link does not work when we copy the pair of
files into a different directory on another terminal. As the pair of files
are always stored in the same directory, is it possible to set a relative
path in MS Access so that it will always point to the Excel file wherever
they reside? Thanks.

Regards,
Adrian

 

[Douglas J. Steele]

Access can't work with relative paths. However, you can always determine
where your MDB file is, and construct the path from there.

In Access 2000 and newer, use CurrentProject.Path.

In Access 97 and earlier, use Left$(CurrentDb().Name,
Len(CurrentDb().Name) - Len(Dir$(CurrentDb().Name)))