Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    write conflict - timestamp problems?

    After nearly a year of my first Access/mySQL client database working fine I've suddenly developed a write conflict error. When I try to change any data of a perviously exisiting record I'm getting the "record has been changed by another user" error in Access 2000 using connector/ODBC 3.51. There is only one user involved and the problem is occurring on multiple platforms (win2k, winXP). I've tried EVERYTHING I could find with no luck:

    no bit fields or floating point fields
    checked all ODBC dsn settings
    no datatype mismatches
    added timestamp column w/ primary key

    Adding a timestamp column seems to be the ultimate solution but after doing so relinking all of my tablesI get the same write conflict error. I think my problem lies in Access dropping the fractional precision part of the mySQL timestamp. I've read that I should use timestamp(0) to avoid this error but that datatype is not being recognized by my version of SQL server.

    ERGH!!! I need help on this desperately, I've been banging my head against this problem for weeks, if not months now. Thanks in advance.

    Lawrence

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Re: write conflict - timestamp problems?

    A cause of this error can be a Memo field losing its pointer to its parent record. If you are using any memo fields (or equivalent), the best thing to do is to download Microsoft's JetComp utility and run it, which will repair your database and fix this specific error with Memo fields. Hang on, I'll just find the address for you......

    Right, found it. Go to :

    http://support.microsoft.com/default...b;en-us;273956

    and download the tool.

    I hope this helps.

    Good luck



    Originally posted by noveltimes
    After nearly a year of my first Access/mySQL client database working fine I've suddenly developed a write conflict error. When I try to change any data of a perviously exisiting record I'm getting the "record has been changed by another user" error in Access 2000 using connector/ODBC 3.51. There is only one user involved and the problem is occurring on multiple platforms (win2k, winXP). I've tried EVERYTHING I could find with no luck:

    no bit fields or floating point fields
    checked all ODBC dsn settings
    no datatype mismatches
    added timestamp column w/ primary key

    Adding a timestamp column seems to be the ultimate solution but after doing so relinking all of my tablesI get the same write conflict error. I think my problem lies in Access dropping the fractional precision part of the mySQL timestamp. I've read that I should use timestamp(0) to avoid this error but that datatype is not being recognized by my version of SQL server.

    ERGH!!! I need help on this desperately, I've been banging my head against this problem for weeks, if not months now. Thanks in advance.

    Lawrence
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Hi Andy-

    Thanks for the suggestion. I've already tried this, however, as I had seen a previous thread about this. No luck. Besides, as I troubleshoot this problem I've been reimporting the entire database via Get External Data >> Link Tables. I assume this would rule out the memo field problem because this process creates new tables each time.

    I'm specifically looking for help on the timestamp issue but any other suggestions are certainly welcome.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    i didn't see this problem on my setup yet. maybe i've just been lucky or maybe it's because i've got TIMESTAMP [...which i believe defaults to TIMESTAMP(14)] in all tables.

    i blindly followed the instruction from the mySQL site:
    Have one more dummy column with TIMESTAMP as the data type, preferably TIMESTAMP(14)

    also from the folk at mySQL:

    4.9 Access returns error like "Another user has modified the record that you have modified", while editing the records. How can I overcome from this? In most cases this can be solved by doing one of the following things:
    Add a primary key for the table if there isn't one already.
    Add a timestamp column if there isn't one already.
    Only use double float fields. Some programs may fail when they compare single floats.
    If the above doesn't help, you should do a Connector/ODBC trace file and try to figure out why things go wrong.

    good luck, izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    Believe me, I've tried all that and scoured the web for reasons why the timestamp column is not doing it for me.

    Access apparently can drop the last few digits of timestamp data which prevents it from sync'ing with mySQL as described here:

    http://support.microsoft.com:80/supp...NoWebContent=1

    (MS knowledge base article #225334)

    I think this might be my problem (as I've tried everything else I can find/think of) but I haven't been able to find a work around. Microsoft simply acknowledges that this behavior in Access is "by design".

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    OK, but the recommended timestamp(14) is
    yyyymmddhhmmss
    ...so it is nowhere close to the millisecond limit discussed in #225334

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Posts
    4
    izyrider-

    that's a good point. nonetheless I am still having problems.

    in some further testing I created a table in mySQL with 3 fields, primay key, timestamp and data. I then imported and linked this table in Access and created a form. I can enter data fine but still cannot edit any data.

    again, I've tried and worked through everything I can think of, DSN config, datatype mismatches, bit fields, corrupted memo fields, and nothing resolves this write conflict.

    I'm totally stumped...

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,739
    are you manually messing with the timestamp field or leaving it to the system?

    (and by the way, i'm a complete newB with my christmas-present linux server and mySQL/innodb setup --- maybe you can get some better help on the mySQL forum on this site)

    izy
    currently using SS 2008R2

Posting Permissions

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