ODBC update errors

  • Thread starter Thread starter Stewart Saathoff
  • Start date Start date
S

Stewart Saathoff

Hello Everyone,

I have an MDB file that contains Forms and Reports that link to a SQL
Database via ODBC. I have no problems adding records, but when I update
them, I receive an error stating:

Write Conflict

This record has been changed by another user since you started editing
it....

Does anyone know how to resolve this? I tried changing the subforms to
requery the parent after an update occured, but that had no effect on the
issue.

Any help would be appreciated.

Thanks,
Stewart
 
This problem usually relates to a failure of the Optimistic locking mechanism
to correctly compare the original values to the existing. stored values to see
if they've changed. This, in turn, usually related to the use of real number
fields which are stored as binary fractions, but passed through ODBC as
Decimal number strings.

You can usually fix the problem by changing any floating point numberic fields
to MONEY type (Currency on the Access side), then make sure you have no date
fields with fractional seconds, etc. If you add a TIMESTAMP field to the
table, however, Access will know to use that for Optimistic locking instead of
having to compare all the field values, and that will avoid the problem
altogether. It also improves the speed of updates somewhat if you have more
than 4 or 5 fields in the table.
 
Thanks, the timestamp works perfectly.


Steve Jorgensen said:
This problem usually relates to a failure of the Optimistic locking mechanism
to correctly compare the original values to the existing. stored values to see
if they've changed. This, in turn, usually related to the use of real number
fields which are stored as binary fractions, but passed through ODBC as
Decimal number strings.

You can usually fix the problem by changing any floating point numberic fields
to MONEY type (Currency on the Access side), then make sure you have no date
fields with fractional seconds, etc. If you add a TIMESTAMP field to the
table, however, Access will know to use that for Optimistic locking instead of
having to compare all the field values, and that will avoid the problem
altogether. It also improves the speed of updates somewhat if you have more
than 4 or 5 fields in the table.
 
I had the same problem in the past. Putting this code would help.

Private Sub Form_Current()
If Me.Dirty Then
Me.Dirty = False
End If

End Sub

SF
 
Back
Top