Field locked on one SQL record

[Jordan]

I have a table on SQL 2000 that I am accessing with an Access 2003 form.
This is a basic data entry form. On 4 or 5 out of the thousands of records
there is a notes field that will not allow me to change or edit data in the
field (ntext on SQL). I can change all the other data in that record and
every other record besides these I can change, delete, and add data to this
field. The only thing I can do in this field is delete the data.

If I use SQL Manager on the server or remotely and open the table I can add,
delete, and modify data in that field, but Access always fails. No computer
that has Access on it can change the data in the field. they can only
delete the text in it an not be able to replace it.

The error is usually a Write Conflict error and asks to drop changes or copy
to clipboard.

 

[Sylvain Lafontaine]

Use the profiler to see what's happening on the server. Also, make sure
that you have used the refresh command (or F5) from the File menu for both
the Tables and the Queries windows.

If you have use the syntaxe « SELECT * FROM ... » then try by writing
explicitely all the fields. It will also be a good idea to put all the
NTEXT fields at the end of the list in the Select statement.

Make sure that the Unique table property is set correctly and if necessary,
create a resync command. You can also try with a dummy resync command (ie.
any stored procedure with the wrong number of arguments; if the primary key
has one field then use any SP with two or more parameters without a default
value).

 

[Jordan]

Ater a lot of digging I was able to find what was causing the problem, but I
still do not understand "Why???"

This is the SQL Table setup:

InspectionNum -- int
OrderNumber -- nvarchar
VendorID -- nvarchar
Part -- -- nvarchar
Rev -- -- nvarchar
ReceivedQty -- int
ReceivedDate -- smalldatetime
InspectionLevel -- nvarchar
AQL -- -- real
Instructions -- ntext
QtytoInspect -- int
QtytoAccept -- int
QtytoReject -- int
InspectedQty -- int
Inspector -- nvarchar
LastChangedBy -- nvarchar
Rejected -- int
RejectDate -- smalldatetime
RTV -- -- int
Rework -- -- int
UAI -- -- int
Scrap -- -- int
ECO -- -- int
Accepted -- int
ConfirmAccepted -- bit
SCAR -- -- int
Note -- -- ntext

The form is a simple entry form that gives the user an "Inspection Number"
and they fill in information like the order the part came in on, the date,
how they inspected it and some notes and etc. There is a form that has this
table as the datasource and all the fields have their datasource bound to
each of these fields. Some of the fileds have drop boxes for selections and
based on things like the Part or the Vendor, other boxes like the
OrderNumber and ACL get popluated or defaults get set.

On the form there is a drop box with a selection list for the ACL field
(datatype = real). The choices are about 20 numbers from .065 to 100. When
a user selects the "Part", the form looks up the proper ACL value and fills
in the field. The problem occurs when the user deceides for this
Inspection they need to use a different ACL number and they select one that
is not the default, but it is on the list. Once they change that value you
can only write once to the Note field (ntext). You cannot go back and edit
the Note field.

On the SQL server and in the Access project there are no relationships set
up nor are there any other criteria that the Note field depends on. I can
edit this field on the SQL server using the management tools however I
cannot even open the table in Access and change the Note once a note has
been entered. I can however, delete the note (make it blank, not null) but
cannot change it to any other text.

I don't know why this solved the problem but I sure would love to understand
why. When I changed the ACL field's datatype from "real" to "numeric" with
a scale of 3 to accomidate 3 digits after the decimal this problem went
away.

 

[Sylvain Lafontaine]

Access use optimistic locking and this cause problem with Real fields
because of a slight difference between the value displayed by Access and the
value stored in SQL-Server. This problem has already been discussed in the
past but it should affect the other fields as well at the same time. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;280730 for example.

Beside you solution, you can try adding a timestamp field to your table.
Access will use this single field for its optimistic locking, thus hidding
the real field conversion problem.