Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Write Conflict - MDB/SQL

Discussion in 'Information Technology' started by Robert_L_Ross, Jul 28, 2006.

  1. Robert_L_Ross

    Robert_L_Ross
    Expand Collapse
    Guest

    We recently upsized data from the BE of a FE/BE MSAccess database to SQL
    Server.

    During the migration process (Development, Quality Assurance, User Acceptace
    and Production) we had no problems.

    I recently requested to have two fields added to one of the tables in the
    Development environment. Suddently, I cannot modify records in that table
    without getting a Write Conflict error. All other tables in the DEV region
    operate as expected.

    The table has a Primary Key and is Indexed. When I try to modify the data,
    I get the Write Conflict dialog but the Save Record option is greyed out - I
    only have the Copy to Clipboard and Drop Changes options.

    I have no idea why this is happening. I've made a completely new blank DB
    and added just two tables (the affected table and one that works) and I get
    the same error when editing it directly in the table view, so I'm sure it's
    not any form or code that is causing the problem.

    We are clueless as to why this is happening. My SQL group tells me I have 2
    connections when I hit the SQL table - but on my 'control' table, I have 2
    connectiosn and it allows edits, on this one modified table I have 2
    connections and it won't let me edit.

    Does anyone have any idea how this happens and how to fix it?

    Thanks!
     
  2. Loading...


  3. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    Assuming you are 100% sure that the ONLY thing you changed was addition of
    two fields, then check the following:

    ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
    Server Table

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

    If the above is not your case, then that "other" person is often your code!!
    If you use code to execute some update to the *same* data as the form, and
    the form still has pending writes, then obviously you have a conflict. the
    solution in this case is simply to force a disk write in your forms code
    BEFORE you run the update code.

    if me.Dirty = true then
    me.Dirty = false
    end if

    .....now run your code that updates data....


    The above code snip thus forces a disk write of the forms data...and then
    you are free to use code to run update stuff.

    So, check the above two things. And, as a side note, for sql server, you
    want

    ** always have a primary key for each field...access *really* needs
    this
    ** always have a timestamp field exposed in all the queries and
    forms -- again, access uses this to determine if the record was updated, and
    has a *harder* time figuring this out without a timestamp field (this is
    especially noticeable in sub-forms).


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  4. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    Add a timestamp field to the SQL Server tables. Doesn't matter what it's named.

    If either of the fields was a Memo field, then Access can't verify that the record
    hasn't changed. Adding the Timestamp field simplifies the task.
    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "Robert_L_Ross" <RobertLRoss@discussions.microsoft.com> wrote...
    > We recently upsized data from the BE of a FE/BE MSAccess database to SQL
    > Server.
    >
    > During the migration process (Development, Quality Assurance, User Acceptace
    > and Production) we had no problems.
    >
    > I recently requested to have two fields added to one of the tables in the
    > Development environment. Suddently, I cannot modify records in that table
    > without getting a Write Conflict error. All other tables in the DEV region
    > operate as expected.
    >
     

Share This Page