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

 

[paulgu]

I think the relative path is probably seen as relative to the folder that Excel is run from, rather than the folder that the file is in.
If the file is on a network drive then if the drive is always mapped to the same letter, eg H, you'll be fine, otherwise use the server name eg \\MyServer\My path\My file.xls.
If the file is on a local drive then you'll have to have it in a folder with the same path on all machines.

 

[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