6
I am using a vba code that transfers some data to an auxiliary table, handles and back to the original table.
How do I solve the competition problem? That is, I didn’t want two users to use at the same time.
2 answers
2
A generic solution, i.e., that does not apply only to Microsoft Access, is to add a column referring to the user ID and reference this column in all queries performed in the table, so as to allow 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 normal table, and we applied this solution. 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. By performing this clean-up, the database system will block other processes that attempt to perform the same operation until the transaction is completed.
In this scenario, a user who tried to execute the operation while another was already running would have to wait for the end of the current transaction.
1
Possible solutions:
1 - Has a flag in some table (main table, temporary table, or any other table of your system) that indicates that the temporary table is in use or is free
2 - Make this task can only be performed by a code, and the code keep track of whether the table is in use or not (as if it were a semaphore or a Singleton) - you will probably need to store this state in a table as well
3 - Create an extra column in the temporary table to signal which user / process is using which records, and make all the rest of the code respect this record
4 - Split the MDB file by having the temporary table run locally on each user’s computer (source: http://www.accessfaq.com.br/webroot/default.asp?http%3A//www.accessfaq.com.br/webroot/detail.Asp%3FPergunta_Id%3D23)
It is better to divide 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 is on each workstation. Read the answer to the question How to split the database and work with linked tables? from the subject "Tables" for more details.
Browser other questions tagged ms-access vba
You are not signed in. Login or sign up in order to post.
The two answers were useful to me, thank you all.
– Regis Santos