Solved

MS Access connecting to MySQL

Posted on 2016-08-17
8
54 Views
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,

Ernst
0
Comment
Question by:ErnstvanderHeijden
  • 3
  • 3
8 Comments
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 500 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

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

Author Comment

by:ErnstvanderHeijden
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?
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 500 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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ErnstvanderHeijden
ID: 41759009
Thanks Dave, I will look into it.
0
 
LVL 83

Accepted Solution

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

Author Comment

by:ErnstvanderHeijden
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

765 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