----------------------------------
VB behind each worksheet/tab that has table(s) that are to be linked into MS Access:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Module1.TableUpdate Target:=Target
    
End Sub

----------------------------------
VB in module1. Note that the prefix to be added to the table name when naming the range is taken from worksheet "DataSources" named range "RangePrefix". Typical values would be "xl" or "rng" but could be anything you choose.


Public Sub TableUpdate(ByVal Target As Range)
'Copyright (c) 2015 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice.

    'Keeps a range object in sync with table definition. Range object is updated each time the table is changed/selected.
    Dim tblTarget As ListObject, thisListObject As ListObject
    Dim cellChanged As Range, rngTarget As Range
    Dim strTableName As String, strRangeName As String, strTblRangePrefix As String
    
    If Target Is Nothing Then Exit Sub
    Set cellChanged = Target
    strTblRangePrefix = Worksheets("DataSources").Range("RangePrefix").Cells(1, 1).Value2
    For Each thisListObject In cellChanged.Worksheet.ListObjects
        If Not Intersect(cellChanged, thisListObject.Range) Is Nothing Then
            Set tblTarget = thisListObject
            strTableName = tblTarget.Name
            strRangeName = strTableName
            If Left(strRangeName, 3) = "tbl" Then strRangeName = Right(strRangeName, Len(strRangeName) - 3)
            strRangeName = strTblRangePrefix & strRangeName
            
            Set rngTarget = tblTarget.Range
            rngTarget.Name = strRangeName
            
            Exit For
        
        End If
        
    Next thisListObject
    
End Sub