relative path in OLEDB string

[Patrick Braunschweig]

Dear All,

I have an Excel workbook with several queries to Microsoft Access. I create
this query via "Data -> import external data -> import data -> then comes a
dialog where I can select my Access DB file". The result is that I can select
a table/query in the Access File and the data pops up in my Excel sheet. So
far it works fine.

When I right-click on the imported data, I can modify the OLE-DB sting. The
issue is that this Access DB and the Excel file should run on several
computers, thus I want to establish a relative path to the Access DB. I.e.,
the OLE DB string should have a relative string like

Data Source=\..\..\11_BPB\100_Model\CentralModel.mdb

instead of an absolute path like

Data Source=C:\Ablage\01_Arbeit\11_BPB\100_Model\CentralModel.mdb

If I enter it like this it won't work. But how can I establish a relative
path then?

Thanks in advance and best regards,
Patrick

 

[JP]

Wouldn't an absolute path always point to the correct location,
regardless of where it was called?


--JP

 

[Patrick Braunschweig]

No, because the whole model consists of a set of files. If I send this model
(with all its folders and excelsheets and the access source db) to a
colleague, then he will probably save that whole stuff in a different path -
that's why it won't work (And I must distribute it within colleagues).

So is there any possibility to set relative paths?

Thanks and regards,
Patrick