- Use linked tables to the worksheets and have a query per linked table to perform the update.
- Use linked tables, but rather than have 5 explicit queries build the SQL in code to perform the update.
- Import all the data, do the updates and spit it back out again.
- Write masses of code involving excel objects, recordsets, lots of debugging and shaking fists at the sky.
You might surmise that I favour one of the first two options, the third is an annoyance that I will contemplate if I have to.
Access queries are perfectly capable of updating/inserting information in linked tables, from that perspective they act exactly the same as "local" access tables.
In an ideal world I'd always recommend having an explicit named query in the database rather than building it, behind the scenes, in code.
This is one of those instances where, despite knowing I'll be told where to shove it, I normally ask the people who produce the spreadsheet whether they'd mind awfully giving me one worksheet rather than lots but I normally find that despite wanting the sky purple tomorrow they're generally very unwilling to help make that happen.
That said if there will always be 5 sheets, there is an initial overhead to having to do everything multiple times but once it's written you've got the flexibility moving forwards to handle the fact that although they said they'll always be the same that sheet3 is now different to all the rest.