Hmm.  The Join to Employee Territories is a red herring. You're only updating the Employee table:

UPDATE dbo_Employees

SET dbo_Employees.LastName = "Leverling2"

WHERE EmployeeID=3;

If you were trying to profile a joined update, this would be a fairer test:

"Discontinue all products supplied from Australia"

UPDATE dbo_Products INNER JOIN dbo_Suppliers ON dbo_Products.SupplierID = dbo_Suppliers.SupplierID SET dbo_Products.Discontinued = True

WHERE (((dbo_Suppliers.Country)="Australia"))

The performance hit in your query is due to the fact that Access wants to bring all the data local, and issue its own updates on the underlying table; your "red herring" query tells Access that 4 rows are involved, so it generates 4 update calls. (I'm thinking the 2nd query Access generates is to get a lock on the record(s) to be updated.)

I agree with Jim Dettman, a PTQ (Pass-Through Query) in my experience runs entirely on the Server at Server speed with none of the extra generated queries and updates that you found using ODBC/Linked tables.

Frankly, I would be interested to know under what conditions Access tries to perform every update itself (like your example showed), and when (if ever) it will send a single update query to SQL Server to perform a multi-record update.