ben.johnston
New member
- Local time
- Today, 11:40
- Joined
- Jun 10, 2024
- Messages
- 7
Hello,
I have an Access Database that multiple people use at any given time, the database is split between a frontend and backend, the backend being an ODBC MySQL linked table (Not the traditional Access FE and Access BE).
The issue I am facing is that since there are 3 or 4 people using it at any given time there are record collisions whenever someone opens up a record at the same time as someone else, we do have our own record locking implementation but that only works when there is a delay between people attempting to open a record.
My understanding as to why this happens is Access loads the remote linked table into memory and does whatever it needs to do with it locally and at some point updates the remote table with any changes that have been made which is where the issue occurs. Everyone loves race conditions!
By the time the record collision has happened it is already open and being worked on by two different people who don't know it has occurred until they attempt to save or move on which is when Access realises what has happened
I am running out of ideas on how to combat this, my next solution is to move the record locking logic out to a web API and have the VBA code query the response from the web API, which would always use the most recent data instead of a cached local copy.
Any solutions to combat this would be greatly appreciated
Many thanks,
Ben
I have an Access Database that multiple people use at any given time, the database is split between a frontend and backend, the backend being an ODBC MySQL linked table (Not the traditional Access FE and Access BE).
The issue I am facing is that since there are 3 or 4 people using it at any given time there are record collisions whenever someone opens up a record at the same time as someone else, we do have our own record locking implementation but that only works when there is a delay between people attempting to open a record.
My understanding as to why this happens is Access loads the remote linked table into memory and does whatever it needs to do with it locally and at some point updates the remote table with any changes that have been made which is where the issue occurs. Everyone loves race conditions!
By the time the record collision has happened it is already open and being worked on by two different people who don't know it has occurred until they attempt to save or move on which is when Access realises what has happened
I am running out of ideas on how to combat this, my next solution is to move the record locking logic out to a web API and have the VBA code query the response from the web API, which would always use the most recent data instead of a cached local copy.
Any solutions to combat this would be greatly appreciated
Many thanks,
Ben