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

 

[SkipVought]

Hi,

I do this often...

dim sPath as string, sDB as string, sConn as string

spath = thisworkbook.path

sdb = "MCD"

sconn = "DSN=MS Access Database;"
sconn = sconn & "DBQ=" & spath & "\" & sdb& ".mdb;"
sconn = sconn & "DefaultDir=" & spath & ";"
sconn = sconn & "DriverId=25;"
sconn = sconn & "FIL=MS Access;"
sconn = sconn & "MaxBufferSize=2048;"
sconn = sconn & "PageTimeout=5;"

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}