Solved

Can not edit older records from Access on MySQL database

Posted on 2011-03-24
5
401 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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 57
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 57
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

739 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