I have a multi user DB split into a front and back end.
I've found that when more than one user at a time attempts to append or update a few thousand records to the main backend table at a time, the append queries on both machines will report a success rate of about 99%, but a few hundred records will fail to write 'due to record lock'.
May I request some information on how best to deal with this?
To be fair to Access, the test I ran was a demanding one. Both machines attempted to append 90,000 records to the same table simultaneously. This is more data than I expect my users to throw at the backend at any one time, though it is not unlikely that 5 or 6 users might happen to try to append or update 4 or 5 thousand records simultaneously.
Am I asking for something too demanding? Or do I just need to change some record locking settings somewhere?
Many thanks.