Multi-user update in Access 2007, Locking issue
Posted on 2013-06-25
Hi. I have a table with more than 1000000 records in Access 2007 database. This table should be simultaneously updated by multiple users. Data has following format: officers update multiple accounts (many to one). Due to business requirements account information reflect data which should be updated constantly by multiple users.
Combined query which is listed below based on Cartesian product leads constant looking issue (Note: the actual data which I have cannot be provided).
1.Is there a better approach to design schema. I have to use Cartesian product in order to put foreign key of officer ID to each account number.
2. Is there any way to avoid locking issue and increase speed of records retrieval: I did the following work
2.1. I Split database between front end and back end, removed any locks (although the best would be to use edit records locking, open database in shared mode
2.2. I consider to do some work similar to replication, but it does not guaranty the locks will be removed.
2.3. I Used indexes
3. Please note that databases have to be put on network which will increase traffic
Account Number Officer ID
If data will be messed, account 1-8 handled by officer 11, account 9-16 handled by officer 12