 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / July 2005Field locked on one SQL record
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. 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).  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >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 [quoted text clipped - 10 lines] > The error is usually a Write Conflict error and asks to drop changes or > copy to clipboard. 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. > 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 [quoted text clipped - 25 lines] >> The error is usually a Write Conflict error and asks to drop changes or >> copy to clipboard. 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Ater a lot of digging I was able to find what was causing the problem, but > I still do not understand "Why???" [quoted text clipped - 87 lines] >>> The error is usually a Write Conflict error and asks to drop changes or >>> copy to clipboard. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.