Re: MySQL and ACCESS

[Christian Schlepphorst]

Hi,

1. Works fine in my experience. You need to install MyODBC on your clients. Then you just export the tables via ODBC from Access to the MySQL server, do some finetuning on it and relink them.
Finetuning means adding primary keys (the get lost during export), autoincrements, and timestamp fields when autoincrement is used (to avoid the Access #deleted# problem on insert).
The Access application stays as it is keeping all the functionality (Access doesn't distinguish between 'real' tables and linked tables.

Some possible pitfalls:
* Boolean fields (Yes/No) have Yes=-1 and No=0 in Access, but get exported as Yes=1. Make sure your application doesn't rely on the sign of bools.

* Currency fields get exported (and relinked) as double. This is ok in many cases. If not, change this on MySQL side to decimal. This is linked to access as text, so you have to change your access application accordingly.

* If you export a table to MySQL and add a timestamp field, you have to set the value of this field to something other than NULL. Recordsets with timestamp NULL might not be editable from Access.

* I experienced a very strange problem when accessing hidden columns of a combobox in an Access form. When the datasource table was Access, the value in MyCombo.column(1) was always there. With a table linked from MySQL, the value was there, unless the main field (MyCombo.column(0)) contained an ü (German U umlaut). With an ü, it was always NULL. I had to retrieve the value with a separate database query from VBA. I couldn't find out whether the problem is Access, MyODBC or MySQL.
Somebody else encountered such a problem?

3. MySQL Admin Client and Query Browser are fine for administration of databases and the server, but not suitable for the end user. For end users you will need a client like Access, PHP/Webbrowser or .NET.

Best regards

Christian Schlepphorst