MySQL Connector/ODBC Developer Guide
To improve the integration between Microsoft Access and MySQL through Connector/ODBC:
For all versions of Access, enable the Connector/ODBC
Return matching rows option. For
Access 2.0, also enable the Simulate ODBC
1.0 option.
Include a TIMESTAMP
column 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
#DELETED#.
Use only DOUBLE float
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
BIGINT column, the
results are displayed as #DELETED#.
The work around solution is:
Have one more dummy column with
TIMESTAMP as the data
type.
Select the Change BIGINT columns to
INT option 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 data after adding a
TIMESTAMP column, the
following trick may help you:
Do not use a table data sheet view.
Instead, create a form with the fields you want, and use
that form data sheet view. Set the
DefaultValue property for the
TIMESTAMP column to
NOW(). Consider hiding
the TIMESTAMP column 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.
On Windows NT, Access reports
BLOB columns as
OLE OBJECTS. If you want to have
MEMO columns instead, change
BLOB columns to
TEXT with
ALTER TABLE.
Access cannot always handle the MySQL
DATE column properly. If
you have a problem with these, change the columns to
DATETIME.
If you have in Access a column defined as
BYTE, Access tries to export this as
TINYINT instead of
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 Failed error 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
(OPTION=10).
Some external articles and tips that may be useful when using Access, ODBC and Connector/ODBC: