Microsoft Access TransferText function: problem with codepage

I开发者_JAVA技巧 inherited a huge, bulky MS Access database and am assigned to solve a problem in it. The problem is as follow...

System A exports its data to a pipeline-delimited .txt file. The files has special characters working correctly, for example the value "Müller" shows when opening this file in notepad or Excel.

Next, the Access DB imports the .txt file and stores the result in an internal employees table. The last name field is of data type "memo". The method to import data from the .txt file to MS Access is as follow:

Call DoCmd.TransferText(acImportDelim, _
                        "tblEmployees", _
                        "tblEmployees", _
                        me.txtImportFile, _
                        True)

After running this import and viewing the employees table I noticed that names with special characters are screwed up. "Müller" becomes "M├⌐ller" for example. I investigated some online help and found out that can include a "codepage" parameter in the TransferText call, so I set it to 65001 (which appearantly is the codepage for unicode):

Call DoCmd.TransferText(acImportDelim, _
                        "tblEmployees", _
                        "tblEmployees", _
                        me.txtImportFile, _
                        True, _
                        , _
                        65001)

Now that I have ran the import script again, I see no difference whatsoever, the special characters are still misformed. I'm running out of steam so I hope one of you has some advise on how to resolve this...

Both versions of your TransferText operation are using a SpecificationName named tblEmployees. What Code Page is specified in that Specification?

Try importing the text file manually. Choose "Advanced" from the Import Text Wizard. Then select Unicode in the Code Page list box. You may need to test with different Code Page selections until you find which one imports your text correctly.

Which ever Code Page selection works, save your choices as a specification and use it in your TransferText command, without supplying a separate CodePage parameter.

Using CodePage=1200 (msoEncodingUnicodeLittleEndian) solved the issue in my case.

there is an unicode list to use in VBA:

http://msdn.microsoft.com/en-us/library/office/aa432511(v=office.12).aspx