MS Access connecting to MySQL

This code has worked perfectly fine for me:

DoCmd.RunSQL "INSERT INTO InschrijversLokaal (id, SubmissionId, ide, idu, inschrijfnaam, email, veldinhoud) SELECT Inschrijvers.id, Inschrijvers.SubmissionId, Inschrijvers.ide, Inschrijvers.idu, Inschrijvers.name, Inschrijvers.email, Inschrijvers.FieldValue FROM Inschrijvers IN '' [ODBC;Driver=MySQL ODBC 5.1 Driver;Server=server.srvr.nl;Database=database;UID=user;PWD=password;FOUND_ROWS=1;BIG_PACKETS=1]"

The moment one Joomla program was updated and proberly changed something in the tables, I received the next error.

Fout 3146 tijdens uitvoering:
ODBC: de oproep is mislukt.
[MySQL][ODBC 5.1 Driver][mysql-5.5.50-cll-lve]Source character set not supported by client (#0)

And Ms Access crashes when I add '';CHARSET=utf8mb4" to the connectionstring:

... IN '' [ODBC;Driver=MySQL ODBC 5.1 Driver;Server=server.srvr.nl;Database=database;CHARSET=utf8mb4;UID=user;PWD=password;FOUND_ROWS=1;BIG_PACKETS=1]"

What do I do wrong?

Thanks in advance, kind regards,

Ernst

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 85

Dave BaldwinFixer of ProblemsCommented: 2016-08-17

'utf8mb4' is a relatively new CHARSET. My older version of MySQL (5.0) doesn't understand it. I would try plain 'utf8' or maybe 'latin1'. More options here: http://dev.mysql.com/doc/refman/5.7/en/charset-charsets.html

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-flags

ErnstvanderHeijdenAuthor Commented: 2016-08-17

When I change the code with utf8 I still get the errorcode. I think the problem lies with MS Access. How do I get MS Access to understand utf8mb4?

I don't believe you can. Latin1 is more likely. Changing the CHARSET does Not change the characters stored in the database.

This old article says that Access does not support UTF8, just double-byte character sets. Maybe a new version of Access supports other character sets. https://support.microsoft.com/en-us/kb/140409

And to keep things adequately confusing, here are pages and pages from MySQL about Character sets and Collations. And connections.

Thanks Dave, I will look into it.

I forgot the link for the last one: http://dev.mysql.com/doc/refman/5.5/en/charset.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Thanks Dave, it helped me a lot to know that MS Access does't cope with utf8mb4. Updating Joomla the users table is changed into utf8mb4. I was able to get the information from an other table in Joomla wich is an utf8_general_ci table.

Everything works fine now.

MS Access connecting to MySQL

Who is Participating?