Import Multiple Access Tables into Excel

[Ryan180]

I've inherited a report which uses some VBA code to run an Access macro and import a single database table into a specified range on the spreadsheet.

Code:

Private Sub CommandButton1_Click()

Dim A As Object
Application.DisplayAlerts = False
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("C:\Folder\Database.mdb")
A.DoCmd.RunMacro "Get_Data"
Application.DisplayAlerts = True


Worksheets("worksheet_name").Range("A2:G600").ClearContents


  Dim strDB As String, strDBPath As String
  Dim strTab As String, strName As String
  Dim strCommandText As String, strConnection As String
  Dim strRange As String, strQuery As String
  Dim qt As QueryTable
     
  '  update these lines as necessary
  strTab = "worksheet_name"
  strRange = "A2"
  strDB = "Database"
  strDBPath = "C:\Folder"
  'strQuery = "tblCycles"
  strQuery = "SELECT * from TABLE1;"
  '  end update these lines section
  
'  strConnection = "ODBC;DSN=MS Access Database;DBQ=" & strDBPath & "\" & strDB & ".mdb;DefaultDir=" & strDBPath & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=600;"
  strConnection = "ODBC;DSN=MS Access Database;DBQ=" & strDBPath & "\" & strDB & ".mdb;DefaultDir=" & strDBPath & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=600;"
  'strCommandText = "SELECT * FROM `" & strDBPath & "\" & strDB & "`." & strQuery & " " & strQuery
  strCommandText = strQuery
    
   With Worksheets(strTab).QueryTables.Add(Connection:=strConnection, Destination:=Worksheets(strTab).Range(strRange))
      
    .CommandText = strCommandText
    
    .AdjustColumnWidth = False
    .FieldNames = False
    .RefreshStyle = xlInsertDeleteCells
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .RefreshOnFileOpen = False
    .HasAutoFormat = False
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .SavePassword = True
    .SaveData = True
    .Refresh BackgroundQuery:=True
  
  End With
   
End Sub

My problem is that I now need to retrieve more than one table as part of the same Command Button click (i.e. button click -> macro runs -> TABLE1 returned to specified range -> TABLE2 returned to another specified range etc.) but don't have the required expertise that will allow me to do this.

Can anyone advise how the above code can be amended in such a way that I can retrieve multiple tables please?

Any advice would be much appreciated!