Competition when using temporary table

A generic solution, that is, that does not only apply to Microsoft Access, is to add a column referring to the user ID and refer to this column in all the queries performed in the table, in order to allow the different users to act simultaneously, each with at most one instance of the application.

In a given project, my team had to use a "temporary" table, which was actually a regular table, and we applied these solutions. We created a suitable index to ensure performance and worked well.

Another approach that effectively blocks competition is to perform operations within a transaction, whose first command is to clear the entire table. When performing this cleanup, the database system will block other processes that attempt to perform the same operation until the transaction is complete.

In this scenario, a user attempting to execute the operation while another was already running would have to wait for the current transaction to finish.

Possible solutions:

1 - Has a flag on some table (main table, temporary table, or any other table on your system) that indicates that the temporary table is in use or is free

2 - Make this task only possible by a code, and the code keep track if the table is in use or not (as if it were a semaphore or singleton) - you will probably need to store this state in a table too

3 - Create an extra column in the temporary table to flag which user / process is using which records, and make the rest of the code respect this record

4 - Split the MDB file causing the temporary table to run locally on each user's computer (source: link )

  

It is better to split mdb into two files: one, containing the tables (back-end) that is in the shared directory of the network; and the other, containing the linked tables, queries, forms, macros and modules (front-end) that resides on each workstation. Read the answer to the question How to split the database and work with linked tables ?, from the "Tables" subject, for more details.