ODBC update errors

[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

 

[Steve Jorgensen]

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.

 

[Stewart Saathoff]

Thanks, the timestamp works perfectly.

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.

 

[SF]

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