Avatar of ErnstvanderHeijden
ErnstvanderHeijden
 asked on

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
Microsoft AccessMySQL Server

Avatar of undefined
Last Comment
ErnstvanderHeijden

8/22/2022 - Mon
SOLUTION
Dave Baldwin

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ErnstvanderHeijden

ASKER
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?
SOLUTION
Dave Baldwin

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ErnstvanderHeijden

ASKER
Thanks Dave, I will look into it.
ASKER CERTIFIED SOLUTION
Dave Baldwin

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ErnstvanderHeijden

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61