MyODBC
Some Tips on MyODBC and AccessAccess
To make Access work:
-
If you are using Access 2000, you should get and install the newest
Microsoft MDAC (
Microsoft Data Access Components) from http://www.microsoft.com/data. This will fix the following bug in Access: when you export data to MySQL, the table and column names aren't specified. Another way to around this bug is to upgrade to MyODBC Version 2.50.33 and MySQL Version 3.23.x, which together provide a workaround for this bug! Note that if you are using MySQL Version 3.22, you must to apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and above to go around this problem. - Set the ``Return matching rows'' MyODBC option field when connecting to MySQL.
-
You should have a primary key in the table. If not, new or updated rows
may show up as
#Deleted#. -
You should have a timestamp in all tables you want to be able to update.
For maximum portability
TIMESTAMP(14)or simpleTIMESTAMPis recommended instead of otherTIMESTAMP(X)variations. -
Only use double float fields. Access fails when comparing with single floats.
The symptom usually is that new or updated rows may show up as
#Deleted#or that you can't find or update rows. -
If you still get the error
Another user has changed your dataafter adding aTIMESTAMPcolumn, the following trick may help you: Don't usetabledata sheet view. Create instead a form with the fields you want, and use thatformdata sheet view. You should set theDefaultValueproperty for theTIMESTAMPcolumn toNOW(). It may be a good idea to hide theTIMESTAMPcolumn from view so your users are not confused. -
Access on NT will report
BLOBcolumns asOLE OBJECTS. If you want to haveMEMOcolumns instead, you should change the column toTEXTwithALTER TABLE. -
Access can't always handle
DATEcolumns properly. If you have a problem with these, change the columns toDATETIME. -
In some cases, Access may generate illegal SQL queries that
MySQL can't 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 will try to export this
as
TINYINTinstead ofTINYINT UNSIGNED. This will give you problems if you have values > 127 in the column! -
If you are using Access 7.0, You should use the option flag
Return matching rows. -
If you are using Access 2.0, You should use the option flags
Return matching rowsandSimulate ODBC 1.0.
from http://www.gw3n.com/mysql/manual_ODBC.html