Keeping the peace between Microsoft Access and MySQL

Well the boss asked me to write up a short blog relating to an issue we had recently. It’s my first one since I joined Gravity Computing so hopefully its not the last.

The issue came about whilst working on a project for a client. The project used Microsoft Access as a frontend for an application with a MySQL database backend. The database tables were linked using the ODBC driver version 5.2 into Access from MySQL and data-bound forms were used to interact with the data.

The issue appeared initially to be intermittent, since a very specific set of actions are required to trigger it. Under normal use, the data-bound forms work perfectly fine with the linked MySQL tables. When you edit a record in the form and then move to the next record, the database is updated and everything is fine. However; if you edit a record, change your mind, and return the fields to the same values they were originally, you may get the following error when you move to the next record.

So it appears you cannot update the record with identical values. Now, you may be wondering if this error is related to some setting in the form used or even a table specific problem. However I can perform the same actions to records in any of the database tables (within Access) and reproduce the error.

Google, as always, helped me to find some other poor soul who had run into the same error. Turns out that the problem can be solved by enabling a connection setting. The complete list of connection options for MySQL can be found in MySQL’s ODBC Configuration Parameters page; MySQL have also published a document detailing other MySQL ODBC Driver Errors and fixes. In this case, it was a simple matter of enabling ‘Return matched rows instead of affected rows’ in your ODBC settings as per the image below.

If you want to generate a connection string in code use:

Const FLAG_FOUND_ROWS As Long = 2

and append “OPTION=” & CStr(FLAG_FOUND_ROWS) to your connection string. Note you can combine more than one of these settings together by adding them up. Some of my current favourites are:

Const FLAG_DYNAMIC_CURSOR As Long = 32
Const FLAG_MULTI_STATEMENTS As Long = 67108864

The former fixes another problem which I can’t quite remember now. The latter one allows you to combine more than one SQL statement into a single string separated by semicolons. This saved heaps of processing time in another project, where a loop was creating individual insert statements which were being sent to the database one by one. More on that another time.

Oh and before I forget, I’ve discovered another important thing while writing this post: if you already have your tables linked in Access, you can update the links using the Linked Table Manager in Access. However if you modify the connection setting you will probably have to close your Access file and reopen it for the changes to take effect.