[Advanced] VBA Query Errors when Directory Path is Too Long

[goldfish]

Sub Repoint_Pivots()
Dim DBDIR As String
Dim PvtItem As PivotItem
DBDIR = ThisWorkbook.Path & "\Databases" 'Storage Place of "Geo.mdb"
 
Sheets("Markets").PivotTables("PivotTable1").SourceData = _
Array("DSN=MS Access Database;DBQ=" & DBDIR & "\Geo.mdb;DefaultDir=" & DBDIR & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;", _
"SELECT S.State, S.City" & Chr(13) & "" & "" & Chr(10), _
"FROM `" & DBDIR & "\Geo`.Locations S")

I get a type mismatch error on the last line. I think the issue is that the Connection part (the first element of the array) is 266 characters (using the long directory path I currently am testing) which I think is too long... I thought there was a limit of 160 characters or something like that. But I haven't been able to break up the connection part of the array without giving me errors... though it seems like the query part can be broken up mid word and it still works fine as each part of the array is just concatenated back together.

Is it possible to write this to account for long path names?

Thanks,
~Goldfish