Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Write conflict with MySQL

    I've created a MySQL database and uploaded two tables from an Access back-end database. To test the potential impact I created a copy of the front-end application and changed the links to point to the MySQL tables. In the FE using the MySQL tables I'm getting a Write Conflict as I navigate from one record to the next. No such error occurs in the FE with only Access table links. The FE is a substantial application with a significant amount of vba code.

    Appreciate any insights on what to look for to find the MySQL problem.

    Marty

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    A quick google search seems to point out several causes for MySQL write conflicts:

    MySQL boolean nullable fields (seems Access doesn't like nullable booleans);
    Table defined defaults
    Timestamp field
    Lack of timestamp field (yes, contradictory with the previous one).

    I have never used MySQL as an Access backend, but I would suggest that you search a bit on each of the possible causes and check your database to see if it suffers from each of the causes. This seems to be quite a frequent situation when using MySQL as a backend.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    mcowen (2013-03-16)

  4. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In my experience, the most common two errors with non-Access backends are the nullable boolean fields, and the lack of a timestamp. We use SQL Server as the backend in nearly all applications, and we always add a timestamp to the table, and we make sure that all boolean fields are required and have a default value set. So I think Rui has pretty well summarized the current advice.
    Wendell

  5. The Following User Says Thank You to WendellB For This Useful Post:

    mcowen (2013-03-16)

  6. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks to both of you for your help. I don't have any boolean fields in the MySQL tables and I also don't have a timestamp field. As I continued my searching I ran across the recommendation to be sure "Return matched rows instead of affected rows" in the COnnector/ODBC definition is selected. After I made this change and re-linked the tables the problem seems to be resolved. Although the advice on Timestamps may still be a good idea to avoid future problems.

    Marty

  7. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Glad you sorted it and thanks for posting with the solution that worked for you.

  8. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    One additional thing, if you already don't know it. I've used a MySQL backend with a couple of applications, but I didn't use any linked tables. Eveything was done with passthrough queries. But for these queries (and I'm assuming this would apply to linked tables), I needed to include "Option=4196355" in the connection string.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •