Post reply

Write Conflict Error msaccess97 and SQL 2000

  • March 31, 2006 at 5:34 am

    #67247

    Hi guys

    I've read the microsoft support solutions and i have tried them without success. let me give you some background.

    i have an access form bound to a customers table in sql. the table in sql has an index on the customer code and has an autoincrement field generated by sql (rowid). i have also put a time stamp field.

    My Access form does not add any records to sql it only allows the user to edit certain fields. I however have buttons on this form that allow the user to set as default what they have entered for one customer, for all. ie if delivery time for customer is 18:00:00 then i have a button that has the following code

    -----initialization etc

    db.Execute "Update CustomerSetup set HourToCountFrom= '18:00:00'"

    me.refresh

    -------------------------

    now, the first time the user clicks the button the update happens without any error message. However any subsequent attempt to update results in the famous --write conflict etc----

    I have 2 questions

    1)is there any thing else i can try (i ve tried the timestamp trick,set to 0 all int and tinyint fields that dont have a value,set defaults to zero on int and tiny ints, removed all nulls in any var and nvarchar fields,removed any floating point fields)

    2) could the problem be that i am using a bound form and at the same time executing code against the table the form is bound on, and hence causing a problem when execution completes on sql server and the bound form doesnt know it yet? and if so, why doesnt the me.requery method simply pick up the changes on the database?

    Sorry for the long post but this thing has me pulling out my hair

  • March 31, 2006 at 8:50 am

    #630155

    Hi. feels a little odd replying to my own post, but i think i've found the problem. This is my Theory.

    When the user enters data into a field and clicks on the update button that uses db.execute to update the db directly, it changes the record in the db. however after this update there is a Me.refresh command that tells the form to update itself. it then finds that the records is different from the one in the buffer and throws up the write conflict error. The solution therefore is to save the record FIRST via the bound form (by clicking in code, the save button) and then doing the database update via db.execute. i did this and the problem is solved.

    Thanks for all those who took the time to try and figure this one out.

    Cheers!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply