• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

ThrottleWorks

Excel Ninja
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.

Code:
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

Excel Ninja
ThrottleWorks
Cannot verify myself ... guessing ...
Have You tried to next?
Code:
' 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.
 
Top