MS Access connecting to MySQL

ErnstvanderHeijden
ErnstvanderHeijden used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Dave BaldwinFixer of Problems
Most Valuable Expert 2014
Commented:
'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
ErnstvanderHeijden

Author

Commented:
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?
Dave BaldwinFixer of Problems
Most Valuable Expert 2014
Commented:
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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
ErnstvanderHeijden

Author

Commented:
Thanks Dave, I will look into it.
Fixer of Problems
Most Valuable Expert 2014
Commented:
I forgot the link for the last one:  http://dev.mysql.com/doc/refman/5.5/en/charset.html
ErnstvanderHeijden

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial