Alleged Record Locking 1

[hughesai]

Hi all,

I have an A97 application which uses MS SQL Server 2000 as the back end database. This has been running sucessfully for a number of years.

I'm having a problem with one record in one table. Every time I edit it in the application I get the "Write Conflict - ... another user has changed this record .... - Save Record, Copy to Clipboard, Drop Changes" error. For me this usually indicates a locked record.

I can change the record in the SQL server table, so the database record is not locked. In the Access linked table I can edit some fields, but in others I get the "Write Conflict" error. These are fields which are an Access "Memo" data type and are mapped to SQL server "Text" data type. None of the fields have huge amounts of data in them - I have seen much larger chunks of text in these fields.

I have exported the offending record to Excel, deleted it and reimported it in an attempt to remove any potential corruption in the data - the result was the same.

I do not get this error on other records.

I do not believe anyone else is editing this record - this problem has been going on for over a week now.

The Access front end is copied to user's hard disks and run from there. The server copy is compacted every night, and I have used fresh copies of this with the same result again.

I've posted this here as I think it's an Access problem and not a SQL server problem.

Does anyone have any suggestions as to what might be happening here?

hughesai

 

[jsteph]

This may be an ODBC problem...sometimes with Doubles and Dates, a precision difference causes the ODBC driver to tell the client that data has changed when it hasn't.

For example, since the ODBC driver doesn't hook directly into the locking mechanism of sql-server, it's only recourse is to re-read the data before update. If the number 1.0000098 is what was read from the db originally, but due to the way the client (Access) now reads the data, it might tell ODBC that it's current value is 1.0000099, and odbc will think data has changed.

Try a select without any of the double/date fields (if possible) and see if that helps.
--Jim

 

[PHV]

Does the SQL Server table have a TimeStamp field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hughesai]

jsteph,

You were right on the money. I had three fields with a SQL Server "real" data type mapping to Access "double" data types. When these were in the recordset I had write conflicts - when I removed them I didn't. ! ! The difference between this situation and the other 3,500 records in the table is that the values were small, eg 0.0000463 compared to the previous smallest value of 0.433. The precision difference was to blame.

PHV,

I assume you were on the same line in that there is a high level of precision in TimeStamp data types which would have generated the same result. I didn't have any TimeStamps, but did have DateTime which I was able to rule out as the problem.

However, I am still somewhat confused - I don't understand why Access would let me save some fields and not save others - it let me change and save the fields with precision problems, but would not let me save the memo / text fields. The problem is no longer there, but I would love an explanation of this behaviour if anyone has any ideas?

For now though, thanks jsteph and PHV for your help.

hughesai