If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > write conflict - timestamp problems?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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



Quote:
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Cavalier King Charles
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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".
Reply With Quote
  #6 (permalink)  
Old
Cavalier King Charles
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #8 (permalink)  
Old
Cavalier King Charles
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On