Truncated MySQL field when connections from MS Access

Question

I have created a linked table to a MySQL table in MS Access 2003. I used the the mysql-connector-odbc-5.1.6-win32 driver I found on the MySQL site. When I view the table I can only see 70 characters in a VARCHAR(255) field. Has anybody had any luck using MySQL from MS Access?

Answer 1

Just a guess, i'm not sure if 70 was an exact number, but:

255/4 = 63.75 (so 63) So that means a 255 byte limit would allow only 63 4-byte characters.

I am not sure if somewhere on the MySQL<->Access it is making the encoding weird.

If your table is using a bigger string encoding, maybe that is limiting the number of characters?

I could also be totally wrong.

Answer 2

It's been a while since I've worked with MySQL tables from within MS Access, but from what I remember, I don't think Access directly uses MySQL's field definitions when linking tables. I can't remember if this was for tables or pass-through queries, but I remember that sometimes it seemed that Access based it's field definitions on the data contained in the first few rows it downloaded.

There may be an optional parameter that you can specify in the connection string to help with setting up correct field definitions. Check out www.connectionstrings.com or MySQL's website for more information.

I actually got away from linking MySQL tables in Access because the performance of queries that joined two tables together was so horrible. I did, however, use pass-through queries quite frequently with a great deal of success. When using pass-through queries, the work of the query is done by the MySQL engine and not the access JET engine, which usually results in much better performance because only the end data is coming down to the client. However, you lose the WYSIWYG benefit of query editing in Access when you use pass-through queries.

One complex solution I would use involved using a pass-through query to automatically create a table that I could periodically manually update. I would have to manually tweak the table definition during set up (since the query didn't pull down field definitions), but once I tweaked the table, I could programmatically update the table with MySQL data. I used this technique when I had to do a lot of complicated things in Access and was pulling down tons of data.