Helpful Information
 
 
Category: MS SQL Development
Write conflict errors

Hi guys.

I have an Access front end to a MS SQL database. I use ODBC to connect the two and have linked the tables into access correctly.

I am having a problem with Write Conflicts. Access is showing an error box every time I try to change records that have links to them from other tables. I am not trying to change keys or any index fields. If I go to the SQL Enterprise manager I can change the records every time with no problems.

Anyone out there got any idea as to what is happening? I have been trying to solve this for over a month now and am completely stuck.

One possible cause is nullable bit fields in the SQL Server table. Access doesn't support that. See MS KB article Q280730 for information about that.

When I had this problem though, that turned out not to be the source. I too found that modifying the row worked in Enterprise Manager but not Access, so to figure out what was going on, I used Profiler to watch the SQL statements generated by Access/ODBC and compare them to those generated by Enterprise Manager.

The way Access and Enterprise Manager detect write conflicts is by using a where clause in the update command that includes the value of every field in the row. If someone else changes the row, the where clause won't match, and 0 rows will be affected.

So far so good. But when there are floating-point number fields in the table, and you try to update an ntext field to something other than null, Access tries to compare the floating point number as if it were an exact, fixed-decimal number. I believe this is a bug in Access. The problem exists in Access 2000 and 2002. I don't know about 2003. Enterprise Manager generates the correct SQL statement to compare floating point values, as does Access, except when updating an ntext field to something other than null.

I can see why this might have slipped past the testers: it only happens with some floating point numbers. Others end up being equal anyway when they go through the type conversion prior to comparison.

The workaround I used was to keep floating point fields and ntext fields in separate tables.

Another workaround is to trim the least significant digits of float before storing it. e.g. use this function

Public Function trimNumberAfter2DecimalPoints(v)
v = v * 100
v = CInt(v) / 100
trimNumberAfter2DecimalPoints = v
End Function



One possible cause is nullable bit fields in the SQL Server table. Access doesn't support that. See MS KB article Q280730 for information about that.

When I had this problem though, that turned out not to be the source. I too found that modifying the row worked in Enterprise Manager but not Access, so to figure out what was going on, I used Profiler to watch the SQL statements generated by Access/ODBC and compare them to those generated by Enterprise Manager.

The way Access and Enterprise Manager detect write conflicts is by using a where clause in the update command that includes the value of every field in the row. If someone else changes the row, the where clause won't match, and 0 rows will be affected.

So far so good. But when there are floating-point number fields in the table, and you try to update an ntext field to something other than null, Access tries to compare the floating point number as if it were an exact, fixed-decimal number. I believe this is a bug in Access. The problem exists in Access 2000 and 2002. I don't know about 2003. Enterprise Manager generates the correct SQL statement to compare floating point values, as does Access, except when updating an ntext field to something other than null.

I can see why this might have slipped past the testers: it only happens with some floating point numbers. Others end up being equal anyway when they go through the type conversion prior to comparison.

The workaround I used was to keep floating point fields and ntext fields in separate tables.










privacy (GDPR)