To improve the integration between Microsoft Access and MySQL through Connector/ODBC:
For all versions of Access, enable the Connector/ODBC
Return matching rowsoption. For Access 2.0, also enable the
Simulate ODBC 1.0option.
TIMESTAMPcolumn in all tables that you want to be able to update. For maximum portability, do not use a length specification in the column declaration (which is unsupported within MySQL in versions earlier than 4.1).
Include a primary key in each MySQL table you want to use with Access. If not, new or updated rows may show up as
DOUBLEfloat fields. Access fails when comparing with single-precision floats. The symptom usually is that new or updated rows may show up as
#DELETED#or that you cannot find or update rows.
If you are using Connector/ODBC to link to a table that has a
BIGINTcolumn, the results are displayed as
#DELETED#. The work around solution is:
Have one more dummy column with
TIMESTAMPas the data type.
Change BIGINT columns to INToption in the connection dialog in ODBC DSN Administrator.
Delete the table link from Access and re-create it.
Old records may still display as
#DELETED#, but newly added/updated records are displayed properly.
If you still get the error
Another user has changed your dataafter adding a
TIMESTAMPcolumn, the following trick may help you:
Do not use a
tabledata sheet view. Instead, create a form with the fields you want, and use that
formdata sheet view. Set the
DefaultValueproperty for the
NOW(). Consider hiding the
TIMESTAMPcolumn from view so your users are not confused.
In some cases, Access may generate SQL statements that MySQL cannot understand. You can fix this by selecting
"Query|SQLSpecific|Pass-Through"from the Access menu.
If you have in Access a column defined as
BYTE, Access tries to export this as
TINYINT UNSIGNED. This gives you problems if you have values larger than 127 in the column.
If you have very large (long) tables in Access, it might take a very long time to open them. Or you might run low on virtual memory and eventually get an
ODBC Query Failederror and the table cannot open. To deal with this, select the following options:
Return Matching Rows (2)
Allow BIG Results (8).
These add up to a value of 10 (
Some external articles and tips that may be useful when using Access, ODBC and Connector/ODBC: