Search code examples
mysqlsqlvbaadodbcorruption

VBA ADODB Query Mysql returns character corruption

Sub hh()
Dim sql As String
Dim rs As New ADODB.Recordset
Dim con As ADODB.Connection
Dim dbConnStr As String
dbConnStr = "Driver={MySQL ODBC 5.2 ANSI DRIVER}; SERVER=localhost; DATABASE=landscape; USER=root; PASSWORD=mypass;"
Set con = New ADODB.Connection
con.Open dbConnStr
sql = "SELECT '東京都' AS tokyou"
rs.Open sql, con
Debug.Print rs!tokyou
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Sub


Returns things like "東・ "

enter image description here I've tried:
1. use adodb.stream to convert query string to utf8, or otherwise convert query result to unicode
2. excute "set names = unicode;" in query
3. add "charset=unicode;" in connection string while open database connection

but none of them works, could you please help me, thanks!

Solution

  • It looks like you are using UNICODE characters. Please note that the ODBC driver comes in two flavours: ANSI and UNICODE. The latter is more appropriate to your situation. Use MySQL ODBC UNICODE Driver and you'll be good. Arigatou!