Problems connecting Access 2000 to a MySQL database

[Jim Leek]

I have set up a simple (only 3 tables) database on a new webserver. We are running MySQL version 4.1.9

Below is an example of one of the tables (tbl_group):

+------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| weekly_group | char(2) | YES | | NULL | |
| coll_finish_date | date | YES | | NULL | |
| target_week | date | YES | | NULL | |
| form_colour | varchar(6) | YES | | NULL | |
+------------------+------------+------+-----+---------+-------+

I tried connecting to Microsoft Access via ODBC (at first version 2.50) and got the following ODBC connection error:

[MySQL AB][MyODBC] ERROR : Client does not support authentication protocol requested by server; consider upgrading MySQL client.

I took this to mean that it was necessary to upgrade my ODBC driver to 3.51.11, which I have done.

I can now connect to the database, and can open tbl_group fine within Microsoft Access. However, when I try to insert a new record within Access I cannot and get the following error message:

ODBC --insert on a linked table 'tbl_group' failed.
[Microsoft][ODBC Driver Manager] SQL data type out of range (#0)

Does anyone know what is going wrong and whether there is a solution?

Cheers, Jim