What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC?
If I understand your question correctly, you are doing a join in one MDB, based on a table in a different MDB and another table in SQL Server.

In this scenario, I would expect Access might need to read all of the data in both tables to be able to complete the join, and may not be able to do any optimizations or filtering until it has both datasets in memory. How big are the two tables? If either of them are large (10,000s of rows), it may take a while to run - although I would not expect it to hang permanently. 

One option that may or may not be practical in your situation is to import the data to local tables before doing the join.  Another generic approach I would recommend is to offload as much of the heavy-lifting to SQL as possible - it is much better suited for working with large datasets.

If you can give some more details about the nature of the hang (temporary? permanent?) and the data (how many rows &amp; columns) that would help us troubleshoot.

A troubleshooting step you can consider is to eliminate all the data columns from your query - just have the Keys. If that works, then slowly add the data columns back in until you can establish which one is causing the hang.