[SOLVED] ADODB query become weird characters with Vietnamese characters

ADODB query become weird characters with Vietnamese characters

I have the code and sample file from an other page about using excel as database in VBA. and i have the same problem with the topic blow: VBA ADODB Query Mysql returns character corruption but my code have:

cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

Is there any way to fix this?

Here is detail about the code( sorry if it's too long to read): http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/

When I use Vietnamese charaters in sheet "data" and running SQL, Vietnamese charaters are all displayed as ???

Hope to receive some guide to fix it.

Sorry, here is the main code For more such as the working code, data, and how it works,... please download full file from link of chandoo above, because i don't know how to attach file here:

            Option Explicit
            Public cnn As New ADODB.Connection
            Public rs As New ADODB.Recordset
            Public strSQL As String

            Public Sub OpenDB()
                If cnn.State = adStateOpen Then cnn.Close
                cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
                ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
                cnn.Open
            End Sub

            Public Sub closeRS()
                If rs.State = adStateOpen Then rs.Close
                rs.CursorLocation = adUseClient
            End Sub

Solution

I have found the answer its is recomended that OLE DB should be used instead ODBC. OLE DB suport unicode charater. I had tried and it worked well.

cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
            ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

should be replace by

connectionstring below
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

thank you for your concern