Connection String In Excel

[mfosterla]

I have an Excel workbook and Access Database that are going to be moved around a lot but always in the same directory. I have a Database Connection String in the Excel workbook as follows:

DSN=MS Access Database;
DBQ=C:\Databases\MCD.mdb;
DefaultDir=C:\Databases;
DriverId=25;
FIL=MS Access;
MaxBufferSize=2048;
PageTimeout=5;

I want to set DBQ and DefaultDir to be the current workbook path. I know I can use ThisWorkbook.Path using VBA. But, How can I accomplish this in the Connection String under conection properties?

Thanks,

Mark Foster

 

[irishjoe]

If you change the DBQ line to this, it might work...

DBQ=CurrentProject.Path & "\MCD.mdb"

 

[mfosterla]

That would work in vb but the setting is in the connections properties dialog box in ms query

 

[irishjoe]

Ah sorry mate, never done that before...

 

[mfosterla]

thanks for the try irishjoe