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 connection properties?

Thanks,

Mark Foster

 

[Guus2005]

For all your connectionstrings:www.connectionstrings.com

 

[ecawilkinson]

Hi Mark,

if you are using an ADODB connection object, do this (where conn is the Connection object) or set the connection string:

conn.ConnectionString = "DSN=MS Access Database;DBQ=" & thisworkbook.path & "\MCD.mdb;
DefaultDir=" & thisworkbook.path & ";
DriverId=25;
FIL=MS Access;
MaxBufferSize=2048;
PageTimeout=5;"

HTH,
Chris