How do i use Thisworkbook.path with this VBA

[JumboCactuar]

Hi,
i have some code which works fine with a set URL but i want to substitute it to use the workbook path

ThisWorkbook.Path & "\MYDB\DATABASE1.mdb"



the code below

Sub getData1()


dat1 = Range("I2").Value


MYSQL = "SELECT * FROM `C:\test\MYDB\DATABASE1.mdb`.TABLE1 TABLE1WHERE TABLE1.EDAT=" & dat1 & " "


    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\test\MYDB\DATABASE1.mdb;DefaultDir=C:\test\MYDB\;DriverId=2;FIL=MS Access;MaxBufferSize" _
        ), Array("=2048;PageTimeout=5;")), Destination:=Range("$a$1")).QueryTable
        .CommandText = MYSQL
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

i usually can use
DBlocation = ThisWorkbook.Path & "\MYDB\DATABASE1.mdb"

then substitute like & DBlocation & but this doesnt seem to work here

any help appreciated

 

[MARK858]

Are you sure that MYDB isn't already included in ThisWorkbook.Path?
What does

Sub Test1()
Debug.Print ThisWorkbook.Path
End Sub

give you?

 

[JumboCactuar]

Are you sure that MYDB isn't already included in ThisWorkbook.Path?
What does

Sub Test1()
Debug.Print ThisWorkbook.Path
End Sub

give you?

yes im sure

this outputs C:\test

 

[JumboCactuar]

Bump

Any suggestions on this?