[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


MS Access connecting to MySQL

Posted on 2016-08-17
Medium Priority
Last Modified: 2016-09-26
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,

Question by:ErnstvanderHeijden
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 2000 total points
ID: 41758970
'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


Author Comment

ID: 41758979
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?
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 2000 total points
ID: 41759002
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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 41759009
Thanks Dave, I will look into it.
LVL 84

Accepted Solution

Dave Baldwin earned 2000 total points
ID: 41759011
I forgot the link for the last one:  http://dev.mysql.com/doc/refman/5.5/en/charset.html

Author Comment

ID: 41759090
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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question