title: Linking multiple source from Access database to Excel file in VBA
body: <p>I am trying to link two tables in Access to my Excel. One is Table_1 and the other is Table 2. But when I ran these codes, the two imported tables are linked to the same source, or say the same data source is imported twice. I tried to solve this problem by deleting the connection and re-connect it. But it does not work. The recorded macro use a line<code>Selection.ListObject.TableObject.Refresh</code>. But the system poped out an error stating that the object is not set when re-running the macro. Full code is below.</p>
  
  <p>I would like to ask how to release the object and connect to another table under this case.</p>
  
  <pre><code>Sub Macro1()
  
  
  Sheets("S6_Inforce").Select
  Range("B1").Select
  Workbooks("Lapse1709_ANALYSIS_v1_S6.xlsm").Connections.AddFromFile _
      "C:\Users\j1sunx\Desktop\201709_Lapse_redo\Step1_Analysis\Lapse Study 201709 S6.accdb" _
      , True, False
  With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
      Connections("Lapse Study 201709 S6"), Destination:=Range("$B$1")). _
      TableObject
      .RowNumbers = False
      .PreserveFormatting = True
      .RefreshStyle = 1
      .AdjustColumnWidth = True
      .ListObject.DisplayName = "Table__1__S6_Inforce_Summary"
      .Refresh
  End With
  Sheets("S6_Terminated").Select
  Range("B1").Select
  ActiveWorkbook.Connections("Lapse Study 201709 S6").Delete
  ' Workbooks("Lapse1709_ANALYSIS_v1_S6.xlsm").Connections.Delete
  ' Selection.ListObject.TableObject.Refresh
  Workbooks("Lapse1709_ANALYSIS_v1_S6.xlsm").Connections.AddFromFile _
      "C:\Users\j1sunx\Desktop\201709_Lapse_redo\Step1_Analysis\Lapse Study 201709 S6.accdb" _
      , True, False
  With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
      Connections("Lapse Study 201709 S6"), Destination:=Range("$B$1")). _
      TableObject
      .RowNumbers = False
      .PreserveFormatting = True
      .RefreshStyle = 1
      .AdjustColumnWidth = True
      .ListObject.DisplayName = "Table__2__S6_Terminations_Summary"
      .Refresh
  End With
  Range("F10").Select
  </code></pre>
  
  <p>End Sub</p>