Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can not edit older records from Access on MySQL database

Posted on 2011-03-24
5
Medium Priority
?
412 Views
Last Modified: 2012-05-11
We have been using a MySQL database backend with an Access front end for years, it works fine until we try to edit an older record and then I get a write conflict from Access.

I can for example open the standard form, open a querry, or directly open the linked table from Access and edit anything entered in the last year and it works perfectly. If I try the exact same thing with something say a year and a half or older, I get a write conflict regardless of what colum I edit, or what I change the data to.

Now here is where things get really weird. I can open MySQL Query Browser (from the same workstation, not on the server hosting MySQL) and change the data by using an update statement, and that works fine. Now when I go into Access and do exactly what I did in the paragraph above, it works fine. It is like MySQL locks the record from Access until the record is edited and then unlocks it.

I am using linked tables through ODBC using the MySQL ODBC connector 5.1 with all default values. MySQL server is 5.0.67. The tables are MyISAM tables and I have run both the Extended check and repair options on the table in question.

Ideas?
0
Comment
Question by:AMPLECOMPUTERS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35208203
<<Ideas?>>

  Make sure if the table has bit fields, that the default sets it to 0 and update any existing records with Nulls to 0.

  Failing that, add a timestamp field to the table, which should make it editable.

  Access (actually JET) has problems with real fields.  Due to floating point rounding errors, it often things a record has changed when it has not.

JimD.
0
 

Author Comment

by:AMPLECOMPUTERS
ID: 35209230
There are no bit fields in the table.

The last field (added around august of 2009) is a timestamp field.

Anything else I can try?
0
 
LVL 58
ID: 35209351
<<Now here is where things get really weird. I can open MySQL Query Browser (from the same workstation, not on the server hosting MySQL) and change the data by using an update statement, and that works fine. Now when I go into Access and do exactly what I did in the paragraph above, it works fine. It is like MySQL locks the record from Access until the record is edited and then unlocks it.>>

  Sounds like something gets updated when MySQL touches it.  Can you compare a record you haven't modified with one where you did?

  Got to be a problem with one of the fields.

JimD.
0
 
LVL 58
ID: 35209370

 and compare both from Access and from MySQL Browser.

JimD.
0
 

Author Closing Comment

by:AMPLECOMPUTERS
ID: 35209539
Found it. Any record that has a value in the timestamp field could be edited, any record without a valid timestamp generated the error. Using the Query Browser inserted a value into the timestamp field which Access could not do.

While you didn't actually fix the problem, you shoved my nose right into it, thanks!
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question