Link to home
Create AccountLog in
Avatar of Zharphyn
ZharphynFlag for Canada

asked on

Write conflict error when writing data back to SQL Server from MS Access

I am developing a program with a MS Access 2010 front end, and a SQL Server 2008 back end.  My boss is having an intermittent error when closing a record.  When the program is committing the data changes back to the BE db, he is getting an error message that indicates that the data has been changed by another user, and my boss' changes cannot be committed.  He gets the choice to either copy to clipboard, or drop changes.  Two key points about this are: 1) the problem is intermittent, and 2) there is nobody else connected to the same database that the boss is using.  What are some of the causes of this error message?  And how can I code in protection to prevent this error from occurring once the application is deployed to our customers?
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Check you bit fields in your sql back end.

Make sure Nulls are not allowed in bit fields and that you have set a default value in the table design for these fields.  

If you have existing nulls in bit fields, run an update query to set them to false.
Floating point field values can also cause this problem.  Try adding a RowVersion field to the table.

For a more complete explanation, look at Best Of Both Worlds on our free downloads page:  www.JStreetTech.com/downloads.  There are a few slides on concurrency.

Cheers,
Armen
Avatar of Zharphyn

ASKER

Sorry about the delay in responding, but illness kept me out of the office.
I checked all the bit fields in the table, and every one is set to not allow Nulls, and every one has a default value set.  I checked the data in the table, and there are no Null values in any of the bit fields.

I also checked on floating point fields.  I do not have any floating point fields, I do have some money fields, which have a default value defined.

Thanks
Brad
Another cause that is not related specifically to SQL Server...

If you have separate forms affecting the same record, open for editing data at the same time, you can run into this message if you allow the user to move from one form to the other without saving the data in between.  The edits made on the separate forms would have the same effect as another user editing the same data.
The program only allows the one form to be open, so it (theoretically) is not possible for another form to be editing the same data at the same time.  I could understand if two users had the same data open from different locations, but not the case in this instance.

Thanks
Brad
mbizup is absolutely correct, if your boss updated the same record from two separate open forms at the same time, this error can occur.

However, this problem does not occur between main form and subform, because Access automatically commits changes to the database when focus moves back and forth between the main form and any subform on that main form.

Another possible issue - is there any code underneath the form, or on a timer perhaps, that might be updating the same record while your boss has it open?  That can also cause the same error.

Armen
I can find no code underneath the form that might be causing updates, and the only timer running in the program also should not be causing any updates, and the timer is triggered with less frequency than how often my boss was experiencing the problem.

thanks
Brad
Hi Brad, did you add a RowVersion field the table as I suggested?  You don't need to do anything in your front-end, just add the field to the table and relink it.  If this solves the problem, then it's definitely a concurrency issue, probably with Bit or Floating Point fields.
I've requested that this question be deleted for the following reason:

None of the expert's advice applied in my case.  I was able to solve the problem myself.
Zharphyn,

Can you post your solution?

If you resolved it independently, you can accept your own posted solution as the answer - which can help other readers in the same predicament.
Recommend accepting the author's answer if posted, otherwise delete.
ASKER CERTIFIED SOLUTION
Avatar of Zharphyn
Zharphyn
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks for following up :)
The reason for the write conflict, is the form is saving some data to the database using SQL prior to the close of the form.  Once the form closes, it is automatically updating the data through the form level bound data source.  Since the data had changed using SQL the write conflict error is thrown.  By explicitly setting the dirty state to false the form edit buffer is updated.