Access front-end with SQL back-end issues
Posted on 2014-02-21
Hi Experts. I have a client with a mission critical Access application which is in production. When I migrated their back-end to SQL last year we encountered tons of write conflicts due to the following:
They have multiple instances of the same table opened on one form - filtered to display different records. Subform #1 shows "A" records, and #2 shows "B" records for example.
When I migrated over to SQL, and even when I was the only user connected I experienced write conflicts. So -- I decided to use temporary tables and update the live tables periodically throughout the session....
Lately the users have been losing multiple records while in production -- it appears that something is happening and for whatever reason (and it appears random) a few or all of the A and/or B records go missing.
The parent record remains. The related records go missing.
I am unable to reproduce the error myself. All users connect using remote desktop to the server, so the environments are the same for all of us. With the exception of power failures which I am assured is not happening, I don't know what to do to rectify this.
I would like to eliminate the temporary tables (which would assure that the records are written in real time to the tables) but need to address the write conflict issues.
Any suggestions would be appreciated. I'm banging my head against the wall -- not to mention dealing with a pissed off client (rightfully so).