Solved

Can not edit older records from Access on MySQL database

Posted on 2011-03-24
5
393 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
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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