Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2002
    Posts
    7

    Unanswered: Write conflict error

    I don't know what changes were made to this SQL Server table but everytime I attempted to update a record through MS Access (this is a link table) this error will occur.

    Write Conflict

    This record has been changed by another user since you started editing it. If you have save the record, you will overwrite the changes the other user made. .......

    .............

    Anyone? Thanks.

  2. #2
    Join Date
    May 2002
    Posts
    299
    Make sure your sql table has a primary key.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    There are a couple of issues with linked tables to SQL Server from access. The most common are issues with existing null values in the records that you are updating.

    For a full description see the following article:

    Article

    Good luck.

  4. #4
    Join Date
    Apr 2002
    Posts
    7

    Thumbs up

    I found the answer in an article in support.microsoft.com. The problem is with my bit field which contains a null value. I need to have a default value into it either 0 or 1 and then run an update query to update all the records of that field.

    Thanks for the replies.

  5. #5
    Join Date
    Apr 2002
    Posts
    7
    Oh, it's the same article that rnealejr is saying. Thanks.

  6. #6
    Join Date
    May 2002
    Posts
    395

    Angry Re: write conflict message

    Hello Bigaon,

    A while back you resolved the write conflict problem which I'm still struggling with.
    This is what you wrote:
    "I found the answer in an article in support.microsoft.com. The problem is with my bit field which contains a null value. I need to have a default value into it either 0 or 1 and then run an update query to update all the records of that field."
    I read the article and did set default value for the field and I have an update statement is updates one record (on the edit record) but it doesn't work. The message still appearing. could you explain why we need to update all the records of that field Or could you provide me the Update statement that work for you.

    Thank you very much!

  7. #7
    Join Date
    Dec 2004
    Posts
    20
    Good day!

    Just as you and many others I had the same problem and after resolving it can offer you a solution and an explanation! The big thanks goes to AchyBreakyHeart Jamie from utteraccess.com whose explanation didn't suit my case completely but helped me to find out the solution and the explanation.

    So, I had a table with begin date and end date fields as a recordsource for a form (Jamie had a query as a recordsource). I wanted to enable a date span input through a form and dates filled in to be remembered so when the form is opened again the dates filled in previously are offered to a user before he decides if he wants the same period for a query or not. User input of dates therefore updates the table and if done in this situation (while the table to be updated is at the same time the form's recordsource) the write conflict message appears all the time. After some time unsuccessfully trying with different Change and Dirty events and re-reading Jamie's explanation I found out the solution.

    EXPLANATION: If you undermine a foundation your house will collapse. So if you undermine the recordsource attempting to update it the write conflict message will appear (application will collapse).
    SOLUTION: Instead of the table as the recordsource and object to be updated I made a query to be a recordsource for my form as follows: SELECT beginDate, endDate FROM TableName, and then using Jamie's solution, in my event procedure before DoCmd.RunSQL "SQL_update_code_in_here" I set the recordsource property of the form to "TableName". Resetting of recordsource property is not needed since when event is finished the setting set to "TableName" cease to exist.

    Thanks again, AchyBreakyHeart Jamie!

    I just don't understand why this simple explanation is not in MS Access2k Help?

    Regards to all!
    BoHr

  8. #8
    Join Date
    May 2002
    Posts
    395

    Re: Write Conflict

    Hello Bohr,

    Thanks for the info.
    I'm not 100% clear on the steps to do what your suggested.
    First you create a query (SELECT beginDate, endDate FROM TableName)as the form's recordsource. Next you set the recordsource property of the form to "TableName". Would that be in code or in Access?
    Then DoCmd.RunSQL "SQL_update_code_in_here" .

    Thanks again!

  9. #9
    Join Date
    Dec 2004
    Posts
    20
    Hi alicejwz!

    That would be in the code.

    When I thought about the explanation I wrote especially concerning undermining of recordsource it seemed that by setting in a code the recordsource property to the table you want to update is exactly the same undermining that caused the whole problem! However I tried once again (I deleted the Me.Recordsource="TableName" piece of code) and the infamous write conflict error occured again.

    So, it really works as I explained and it seems that Access just doesn't like the undermining of the permanent recordsource property that you set under Data tab of Form's property window.

    Regards!
    BoHr

  10. #10
    Join Date
    May 2002
    Posts
    395

    re: write conflict

    Thanks Bohr!
    I will keep them to my notes for when it occurrs again.

    Thanks!

  11. #11
    Join Date
    Dec 2004
    Posts
    20
    Quote Originally Posted by BoHr
    Hi alicejwz!

    So, it really works as I explained and it seems that Access just doesn't like the undermining of the permanent recordsource property that you set under Data tab of Form's property window.

    Regards!
    BoHr
    Hi alicejwz!

    You're welcome!

    Nevertheless there is an imprecision in the quote above!

    Since with the DoCmd.RunSQL "SQL_update_code_in_here" I tried to update the "TableName" table which was the foundation of the recordsource query "SELECT beginDate, endDate FROM TableName" but not the foundation of the form itself it is incorrect what I wrote, namely, that "Access just doesn't like the undermining of the permanent recordsource property that you set under Data tab of Form's property window.". Access just doesn't like updates of ANY of foundations either updates of form's permanent recordsource (be it a table or a query) or updates of a "remote" foundation (like table is when a query based on it is form's recordsource). For some reason Access requires the change of a recordsource (from a table to a query based on it or vice versa) in a VB code BEFORE the update code (DoCmd.RunSQL "update_code_here") and it works!

    Whew! Hope this is it now and will not have to correct myself on this again!

    Regards!
    BoHr

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •