Unicode - Access and MySQL

[Christophe Desmecht]

I'm developping a database with Access front-end.

The idea behind the application is that it will open Excel files in the background in an attempt to do a 'smart' import (meaning it will import only those things that need to be imported). The data being retrieved is customer information in all possible languages ranging from english, french, scandinavian languages, german up to asian languages (japanese, etc...) The VBA code works fine up to here.

However when I checked the data I was getting from Excel, I noticed ??? (question marks) appearing. I did some checking using the strconv() vba code and it turns out Access has no way of properly displaying unicode characters while debugging. I checked what was actually being retrieved by putting the data into byte arrays and I could see that multi-byte characters were being retrieved. So up to here everything was ok. Even though I was seeing ??? (question marks), internally they were still the proper unicode characters.

I then set up a mysql connection using DSN and wrote the imported data to several MySQL tables. The database, as well as the tables are using character set UTF8 and collation UTF8_general_ci. The data being inserted into the database were actual question marks.

Several attempts later, I'm still at a loss.
This is what I tried:
- added 'set names utf8' to connection string in VBA
- added 'set character set utf8' to connection string in VBA
- added both 'set names utf8' and 'set character set utf8' to connection string in VBA
- set connection, table and db character sets to utf8 and collations to utf8_general_ci
- set connection, table and db character sets to utf8 and collations to utf8_bin

I verified all this with the 'show variables' command and they are properly set up.

So why am I still getting question marks in my mysql tables?

This is my connection string:
DRIVER={MySQL ODBC 3.51 Driver};UID=userid;PWD=pw;SERVER=servername;DATABASE=dbname;

I'm running Office 2002 on Windows XP client, with MyODBC 3.51 installed
The server is running MySQL Server 4.1.9 on Windows NT

If you need more info I can gladly provide it.



Edited 2 time(s). Last edit at 06/03/2005 12:35AM by Christophe Desmecht.