Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Write Conflicts - Data Changed by Another USer (Access 2002, SQL Server 2000)

    I'm having a lot of trouble with my Access front-end and SQL Server back-end client-server system. I just released a new version of my client side .mdb file. In this version, I made changes in the Current Event of my data entry form.

    Until now, multiple users were able to open a record simultaneously. This created problems when one user would make a change and the record would be locked. Only, the other user who had it open didn't know this and made changes too. Then, when user #2 tried to save her changes, BOOM, she'd get a message that the Data was changed by another user and her changes couldn't be saved.

    So, I created a table in which the ID of the record was recorded every time a user opened a record (the code to do this was placed in the Form Current event). Then, if another user tried to open the record, my code checked the table to see if the ID was there. If not, it allowed the record to be opened. If yes, it prevented it. Simple, right?

    Well...after releasing this new version, suddenly, everyone and his grandmother started having this Write Conflict problem. I don't know how or why this is happening. If anyone can shed light on the situation, I'd be most grateful.

    Thanks so much.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    How do you write the ID to the new table? Do you append a new record, or do you overwrite the first record every time? I the latter, that is probably the cause of the conflict (and it won't do what you want)

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    I insert a new record. I can't overwrite the old one because I have multiple users, and each one is opening records. the table is on the backend and is supposed to store the ids of ALL open records by ALL users. Point to ponder - I'm having people switch back to the old version and they're STILL having this problem. Ouch.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    Ouch indeed. The cause must be somewhere else then...

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    But where?? This happened very infrequently before the "new" version was implemented (and then retracted).

  6. #6
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    In trying to research my problems, i came across the concept of "row versioning". I don't really get what it is. Can anyone enlighten me and perhaps explain how implementing it will help to solve my problem?

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    Hi TJ
    Sorry for the lengthy delay in responding to your post - I've been traveling and without web access for most of the time. I'm not sure "row versioning" will help with your problem, as it involves considerable tweaking of your SQL Server database, but this MSDN article gives some of the basics. Unfortunately, with an ODBC connection, I'm not sure that you would be able to manipulate the cursor to get the desired result. We have typically tried to manage this with the basic Access concurrency issues and the ODBC error message that is returned with the error. The bottom line is that you don't want users sitting with a form open that they start an edit on, then get interrupted or distracted and leave for a period of time and then come back and try to save a record and someone else has edited it. So simple forms that are quickly completed work best - in addition sometimes you may want to look at partitioning job responsibilities so that there is less likelihood of concurrency problems.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    <P ID="edit" class=small>(Edited by WendellB on 09-Mar-04 11:36. Activate link to MSKB - btw there is a shortcut markup that will do this for you [ mskb=280730 ] without the spaces.)</P>I've been doing some research in the Microsoft newsgroup for Access users. It seems that a lot of people who have Access front-end and SQL Server back-end systems are having these Write Conflict problems. This is an acknowledged Microsoft bug (as I just discovered). The following kb article has details:

    http://support.microsoft.com/?id=280730

    I got so excited when I found this article that I immediately kicked everyone off of the system and changed the datatypes of my bit fields to tinyint and my real fields to decimal. BIG MISTAKE!!!! I messed up and lost all of my floating point data. I had to restore from a backup. Now I have to research which datatype is the best one to switch my "real" fields to. However, at least I know that I'm not crazy and that this is a known problem <img src=/S/notmyfault.gif border=0 alt=notmyfault width=15 height=15>

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    If you do have a number of Real fields in your tables, this may well be at the heart of your problem. The simplest solution is to add the timestamp field to the SQL table and that will sort things out - apparently the ODBC driver does use it for concurrency checks, so the timestamp field (as suggested in Books OnLine) would effectively implement "row versioning." You don't need to include the field in any queries, and it won't be editable (for obvious reasons). The alternative would be to go to a Decimal field type, but in that case, Access is likely to think you are dealing with currencies, and do some formatting you would care for.

    We seldom use real fields in what we do, so this isn't a problem I have ever run into. Hope this solves your problem. (BTW - this won't solve true concurrency issues where two users try to edit the same record.)
    Wendell

  10. #10
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    I have 4 fields of the type Real. I also have a timestamp field, but it seems like it's not helping me. Do I need to include it on my form as a hidden bound control or something? What do you use instead of type=Real. Will type=float give me the same problems? In what way is access likely to treat Type=Decimal as currency? Will it put $ when formatting the data?

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    If adding the timestamp field isn't solving the problem, then I suspect you have real concurrency problems with two users trying to update the same record. I don't think changing to a decimal field type will give you any benefit.
    Wendell

  12. #12
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    I'm quite sure that the concurrency problem is "all in Access's mind" since it's happening way too often to be real. I've even recreated the problem using a test table and a test form - I was the only user. I'll be in early tomorrow to make my changes while e/o else is asleep. I'll let you know if they solve the problem <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    Well, there are known form design issues and problems with MDAC versions that can cause ODBC errors that get interpreted as concurrency problems but aren't necessarily. I think we touched on this in another post some time ago, but are you using triggers on the tables which are being updated, and are your errors occuring on edits of existing records or the addition of new records?
    Wendell

  14. #14
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write Conflicts - Data Changed by Another USer (Access 2002, SQL Serve

    Nope, no triggers. I changed the data type of the "real" fields to "decimal" with scale of 3. I changed the "bit" fields to "tinyint". I think and hope that the data has been preserved. There is a timestamp field already in the table. I removed all NULL values for these fields just in case. I relinked my Access tables and redistributed the front-end. And now I'm just praying <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

Posting Permissions

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