?
Solved

Can not edit older records from Access on MySQL database

Posted on 2011-03-24
5
Medium Priority
?
407 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

741 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