MS Access - Take source for Access database based on cell value in excel

[ThrottleWorks]

Hi,

I am using below code to import MS Access database in excel.
This code is written in Excel file. I have only one table in Access database.

Is it possible to give cell reference instead of full path in the code.
I am trying to change below line but not able to do so.

"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\AA\AA\AA\AA\AA.accdb;Mode=ReadWrite", Destination:=Worksheets("DataBase").Range("A1")).QueryTable

I am trying to replace ‘=\\AA\AA\AA\AA\AA.accdb’ with ‘MyDataSource’.
Can anyone please help me in this.

Sub OPENCONNECTION()

    Dim MyDataSource As String
    MyDataSource = Worksheets("Sheet1").Range("B1")
  
    Worksheets("DataBase").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\AA\AA\AA\AA\AA.accdb;Mode=ReadWrite", Destination:=Worksheets("DataBase").Range("A1")).QueryTable
              
        .CommandType = xlCmdTable
      
        'Name your table below
        .CommandText = Array("MyDatabase")
      
        'Refresh on open?
        .RefreshOnFileOpen = False
      
        'Refresh rate (in minutes)
        .RefreshPeriod = 20
      
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[vletm]

ThrottleWorks
Cannot verify myself ... guessing ...
Have You tried to next?

' original part
Source:= "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\AA\AA\AA\AA\AA.accdb;Mode=ReadWrite"
' modified part
Source:= "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Data Source" & MyDataSource & ";Mode=ReadWrite"

and take care that the 1st character is = in Your 'MyDataSource'-variable.

 

[ThrottleWorks]

Hi sir,

Thanks for the help. Changed as advised by you. Have a nice day ahead.