Write conflict with MySQL

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Glad you sorted it and thanks for posting with the solution that worked for you.

Reply | Quote