Generally it is poor practice to actually update multiple tables from any form/query. Although the RecordSource query for a form commonly contains joins to lookup tables, the lookup data isn't updated on the form since it is too dangerous. People loose sight of the fact that the lookup data relates to many records, not just the one they are updating.
We're going to know more about what you are doing. Is this a form where you are having a problem? Is it a query? If it is an update query, that is frequently a sign of some design flaw.
Are you attempting to control record locking? Your best option is to stick with the defaults. Access is quite capable of handling contention for recourses.
One particular problem with updating two tables at once is what is called deadly embrace. Updates are always done within a transaction which ensures that the entire update succeeds or if any part fails, all pending updates are backed out. If you have two users who might be doing different processes that involve one or more of the same tables you have the potential to lock up the whole system as you seem to be doing: In order for the transaction to work, the database engine needs to get a lock on the data from ALL tables being updated.
So, userA starts a process and Access enqueues table1 but UserB sneaks in with another process and enqueues tabld2. When the UserA process goes to enqueue table2, it can't because UserB has it locked and UserB can't enqueue table1 because UserA has it locked -- deadly embrace. Neither can proceed because some required object is locked by a different user. The database engine loops and retries but eventually either locks up (some versions) or just fails (other versions). That is why we don't normally try to make one update dependent on another.
Using recordsets will not resolve anything. We need to understand WHY you have to update all these tables in the same query because there is most likely a better option.