July 1, 2015 at 8:33 am
Records created in Access can not be updated even by the person who created the record. Message is "Write Conflict". Locks remain for days.
I have been able to update the records using a specific record Access query or SQL Server edit but not using the Access table edit or Access forms. There were no problems when the application was Access FE and Access BE.
Details follow.
I have a SQL Server (2008) Backend (BE) and a MS Access (2003 -MDB style) Frontend (FE). I added a MS Access application that had been stand alone with MS Access FE and BE. The tables had been matched and used for several months in a test mode.
The problem is limited to two users. Two user were able to create records with Access FE Forms that no one was able to update. After three days, the records are still locked when accessing on Access. These records can only be updated using Access queries or SQL Server table editing. There are six bit fields; all have default values (but I added the default values after noticing this problem -- new records seem to work okay after forcing a default value to the bit fields).
The application has been working in Access FE/BE for years. Now, any user including the admin gets a WRITE CONFLICT when trying to modify any value of the record by these two users. The box says
"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
"Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes."
There are two possible selections (in addition to save record greyed out)
"Copy to Clipboard" "Drop Changes"
As a test, I created a new MS Access front end with no entries. I linked to the table that is locking and used the Access OPEN to view the table. The records that were locked in the Production application are still locked and can not have any values updated.
All users are in the same time zone in five different physical locations. Users are forced off the Access FE if they do not use the system after 45 minutes. All users are supposed to log of the Access application at the end of the day. There are no LDBs on the various front ends between 5:30 pm and 7:30 am daily. During the time that no one is using an Access front end, my simple one linked table is still showing the lock ("Write Conflict").
Should I give up on the currently locked records and just re-enter them?
Any suggestions?
July 1, 2015 at 9:04 am
This problem has been corrected.
CAUSE: Bit fields did not have default value specified
FIX:
--New records do not have problem because default value for bit fields has been specified.
--Old records were updated with query setting the bit values to zero. Before updating each record, verified that the value shown by Access was 0; then updated to 0 with an "update query".
Now records can be updated by users with appropriate authority using Access forms.
July 1, 2015 at 9:06 am
Thanks for the update. I was just about to say re-enter, but it's good to see a default helped here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply