A union query is still a query, unless you are appending all of the data into
one table. A hardcoded table would include all of the data from all of your
spreadsheets (another copy), and may be easier to use.

This code assumes that all of the linked tables in your database have the
same structure (or at least the field names all exist in another table called
NewTable). Here is some quick and dirty code that will move all of the data
from all of the linked tables into this new table.

Sub CopyAllTablesToOneTable()
Dim tbl As TableDef
Dim strQuery As String
Dim qry As QueryDef
'this should call the new table where the data will reside
Const MainTable As String = "NewTable"

   'clear out the main table before starting
   Set qry = CurrentDb.CreateQueryDef("", "delete * from [" & MainTable &
"]")
   qry.Execute
   
   For Each tbl In CurrentDb.TableDefs
       'this will only look at linked tables (assuming that all linked
tables are ones with similar info
       If Len(tbl.Connect) > 0 Then
           If tbl.Name <> MainTable Then
               strQuery = "INSERT INTO [" & MainTable & "] SELECT a.* FROM
[" & tbl.Name & "] AS a;"
               qry.SQL = strQuery
               qry.Execute
           End If
       End If
   Next
   
   Set tbl = Nothing
   Set qry = Nothing
End Sub

Please let me know if you need any more assistance.