Easiest way (and for main part no VBA is needed):

Into your Master workbook, add a hidden sheet. Now create 4 ODBC queries with dataranges in this hidden sheet - firstmost one placed at top and having headers returned. The rest of queries dataranges must be placed below it, without header row, and for every datarange you have to reserve enough of records (any number of empty records don't affect the end result in any way - so you can be generous with this). All gueries must return same fields in same order - so when source tables have different column orders, you have to adjust queries accordingly. All 4 queries must be set to be updated on open, and must overwrite existing rows and clear abundant ones - i.e. deleting is NO!

Then on hidden sheet you define a fixed named range covering all query dataranges + reserved empty rows on hidden sheet. And on sheet where you want to display consolidated data, you create an ODBC querie based on named range on hidden sheet, with WHERE condition excluding all empty rows. And set the query to be refreshed on open.

Optional part:
There may be a problem with queries - after some version of MS Access I enconutered a problem, where the query worked when created, but didn't work when workbook was opened next time. To get it to work again, I had to edit the datasource - then the query worked OK until next session (I suspect this may be because in our company we have 64-bit Windows in all computers, but 32-bit MS Office). As solution, I did write an Open event for such files, which edited all datasources of queries to Excel workbooks in this workbook. Additionally the event also checks the location of workbook, and in case the source of query is in same workbook and the location was changed, the event edits this too. So user can move the workbook to other location, and all queries will work.