Sub getDataFromAccess()

    Dim adoconn As ADODB.Connection
    Dim adors As ADODB.Recordset
    Dim sql As String
 
    Set dbcon = New ADODB.Connection
    'Access 2003
    'dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & "Database location****" & ";", usernm, pword
    'Access 2007 or after
     dbcon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "Database location****" & ";", usernm, pword
    Set dbrs = New ADODB.Recordset
  
    Call pasteData(dbcon, dbrs, "Table or Query Name in Access")
  
    MsgBox "Done!"

    dbcon.Close
    Set dbcon = Nothing

End Sub

Sub pasteData(dbcon, dbrs, tblName)
    sql = "SELECT * FROM " & tblName
    dbrs.Open sql, dbcon
  
  
    With ThisWorkbook.Worksheets(tblName)
        .UsedRange.Clear
        For iCols = 0 To dbrs.Fields.Count - 1
            .Cells(1, iCols + 1).Value = dbrs.Fields(iCols).Name
            .Cells(1, dbrs.Fields.Count).Font.Bold = True
        Next
        .Range("A2").CopyFromRecordset dbrs
    End With
  
    dbrs.Close
End Sub