Seem like many people have encountered this daunting "data type mismatch in criteria expression" error, so do I. Some people have this when they wrote VB scripts in MS Access, but my case is not related to any code at all. I encounter it just when I try to output some data from mysql table to a static report in MS Access. So many solutions on the web could not fit into my situation.
After several days of pulling hair, I finally find the cause.
I used BigInt as one of my Mysql table field type. It seems MS access or ODBC doesn't recognize this type. After I change it to int, everything works fine now.
Microsoft really should provide more information to this bug. The error message given is too general which doesn't help in such complicated situation.