Compatible and recommended data types for MS Access frontend / MySQL backend

Question

I need a list of recommended MySQL data types to use when using Microsoft Access as the front end. Can anyone point me to a succinct article on the net, or post a list here please?

Answer 1

Check out this: Using Connector/ODBC with Microsoft Applications

For all versions of Access, you should enable the Connector/ODBC Return matching rows option. For Access 2.0, you should additionally enable the Simulate ODBC 1.0 option.

You should have 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).

...

Access cannot always handle the MySQL DATE column properly. If you have a problem with these, change the columns to DATETIME.

....

Answer 2

Here's a comparison of MS Access, MySQL, and SQL Server datatypes.

There are a lot of tricky issues to watch for; in some cases, Access and MySQL give the same name to different data types, e.g.

• TEXT in Access is 255 characters (similar to MySQL's TINYTEXT)
• TEXT in MySQL is 65535 characters (similar to Access's MEMO)

So if you use a TEXT field in MySQL, you'll have to access it as a MEMO in Access.

Number types can be tricky, too. MySQL has both signed and unsigned versions of each type, but Access doesn't. For example,

• BYTE in Access is equivalent to MySQL's TINYINT UNSIGNED
• INTEGER in Access is equivalent to MySQL's SMALLINT (signed)