Results 1 to 6 of 6
  1. #1
    trb5016 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71

    Write Conflict Errors with SQL-Access DB

    My backend is an SQL server and the front end uses linked tables in access. everything works fine except the following situation.

    If a user makes a change on a form, and then changes it back, then tries to move to a different record (or anything that causes an update I think) I get a write conflict error with the options to copy or drop. You can choose 'drop' and nothing is lost - because nothing was done really.

    Think of checking a check box, then unchecking it and moving away.

    I've error trapped this in the OnError event, but suppose the user clicks a "goto next record" button. Even if I trap the write conflict error it's followed by a "Can't go to the specified record" error.

    My nav buttons are coded so that if you're on the first record the "previous" button is disabled so that normally handles the type of error. But that is moot when the write conflict messes it up.

    Any ideas?



    (Thus far I've just told my users not to do that lol, but I'd like a better solution)

    EDIT: In addition, I've tried adding timestamp fields as recommended by several people to fix the write conflict. This results in a 7776 error which as far as I can tell is unfixable

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Even though the user has changed the value back to the .OldValue the Dirty flag is still set so Access will try and save. Are you using queries as the RecordSource of your forms or directly to a table?

  3. #3
    trb5016 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71
    This particular case the recordsource is a table. I do have other instances where the same issue occurs but the recordsoure is a query

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Each form has its own RecordLocks and RecordsetType properties. To what do you have these properties set? Queries do not have nearly as much of an issue with locking errors as do tables.

  5. #5
    trb5016 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71
    I'm pretty sure everything is set to "Lock edited record" (that's the default I'm pretty sure)

    I can't confirm that till Monday though when I'll be going back to work. would you recommend making querys that replicate the table? Is that more stable?

    This will eventually be used by 2-3 users simultaneously

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    There has to be some overwhelming reason for me to use a table directly, and I can't think of any good reason at this time. Here's a link to the best article I've read on MultiUser databases in Access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. .ocx Files Errors
    By darshita in forum Import/Export Data
    Replies: 9
    Last Post: 12-07-2009, 07:36 AM
  2. 2 combo boxes - Conflict
    By JimS in forum Forms
    Replies: 3
    Last Post: 10-13-2009, 03:49 PM
  3. Write Conflict Error
    By botts121 in forum Access
    Replies: 5
    Last Post: 09-25-2009, 12:11 PM
  4. Write excel expression in access
    By ktmchugh in forum Queries
    Replies: 0
    Last Post: 07-01-2009, 02:47 PM
  5. Access 2002 Front-End Inconsistent Errors
    By janetb in forum Security
    Replies: 1
    Last Post: 07-28-2008, 12:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums