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

[Kaak]

I use something like this:

Dim Connection As ADODB.Connection
Dim sConnect as String

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & Thisworkbook.Path & "\pbo.mdb';"

Set Connection = New ADODB.Connection
Connection.Open ConnectionString:=sConnect

.................
.................

Connection.Close
Set Connection = Nothing