Write conflict - strange behavior

[RichO]

I know this topic has been covered in a number of threads but what is happening here is very strange.

I have an Access 2000 front end with a SQL Server back end and there is a problem with just one field in only one record that is triggering a write conflict. I can go into Enterprise Manager and change the value of that field but if I attempt to change it anywhere within Access I get the error. The field does not contain <NULL>. I can change any other field within this record and I do not get an error. I can even delete the text within the field from Access and I do not get the error, but if I attempt to change the text it in any other way I get the error.

Any idea what could be causing this?

 

[SQL_Hell]

Hi there

I dont tend to answer these questions anymore because its been a few years now since I have dealt with MS Access and I am a bit rusty, however I will try help if I can.

I have a few questions:

Is this access project or MDB?
What version of SQL?
What is the data type of this field?

 

[RichO]

This is an MDB...

I think it's SQL Sever 2000 because it was installed in late 2001.

The data type of this field is 'ntext'.

I gave up trying to figure it out and ended up completely deleting the record. Then I re-entered the information from scratch and now it doesn't give me any more trouble with that field. Don't know what the problem was with it but I won't be able to try anything out now because it's been deleted.

Thanks for offering to help though.

 

[SQL_Hell]

Ok cool, for reference I would have tried changing the field to varchar (this is the prefered text data type for sql server).

Also I notice you are using ntext, you should not use any fields with N in front of them because those fields are text fields with UNICODE compatability, I very much doubt you need UNICODE compatability and a nvarchar field is twice the size of a varchar field meaning your database will be unnecessarily large. The access upsizing wizard creates all text fields as ntext or nvarchar but it is not needed.

 

[RichO]

Many of the text fields within this database are varchar type. It was probably the upsizing wizard that is responsible for the ntext data type because the DB originally did not have a SQL server back end. I did not originally install or design the DB.

I will definitely reference this if I run into any similar problems. I may even go in and change all of the "n" data types to save on space.

Thanks for your help.