Connection String Relative Reference Requiring DSN

[Juglaz]

Hi guys,

I've got a workbook that pulls data from an access database. MS Query created the following connection string which works just fine:

DBQ=C:\Users\t5264ab\Desktop\Dashboard\Claim Analyzer Total 13MY YTD.mdb;DefaultDir=C:\Users\t5264ab\Desktop\misc docs;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;</SPAN>

However I need the paths to be relative and point to a file located in the same folder as the workbook. I used ThisWorkbook.Path:

DBQ=" & ThisWorkbook.Path & "Claim Analyzer Total 13MY YTD.mdb;"DefaultDir=" & ThisWorkbook.Path &;"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

After making this change, when Excel goes to refresh the data it gives me a pop-up to Select Data Source. I don't understand why I suddenly need a DSN with a relative reference. Can somebody please help? Thanks.
</SPAN>

 

[xenou]

Possibly just try:
DBQ=" & ThisWorkbook.Path & "\" & ...

The path property doesn't provide the trailing separator that you need.

 

[Juglaz]

Possibly just try:
DBQ=" & ThisWorkbook.Path & "\" & ...

The path property doesn't provide the trailing separator that you need.

Still getting the same request for a Data Source. That may have been one of multiple errors in my code though.

 

[xenou]

Is this something you are doing in a macro?

 

[Juglaz]

Is this something you are doing in a macro?

No macros. Just trying to get the connection string right.
The string shown leads to the Select Data Source dialogue box. With the absolute reference path it didn't need a DSN.

 

[xenou]

Hi,
Sorry - no can do. You can't use ThisWorkbook.Path as a dynamic variable in the dialog. I think all you can do is either set it with vba or allow the user to select it when the popup comes up.
ξ