2

I have a Database in Access 2003 that needs to work with foreign language characters. The characters show up fine in the table. However, when VBA tries to read them it cannot do it.

As an example, the column ANSWER_TEXT from cf_Answer displays: 佛吉尼亞海灘

But a select statement in VBA:

sqlstmt = "SELECT ANSWER_TEXT AS ans_text FROM cf_Answer"
Set rst_a = dbs.OpenRecordset(sqlstmt, dbOpenSnapshot)

rst_a![ans_text] returns ??????.

I know this has something to do with UTF-8 encoding but I cannot find a way to set it. Is there a way to set it in the table? Currently, ANSWER_TEXT is of datatype memo. Or perhaps there is a way to set VBA to understand those characters?

Can anyone at least point me in the right direction?

Erik A
  • 31,639
  • 12
  • 42
  • 67
Jake Zeitz
  • 2,429
  • 3
  • 23
  • 43

1 Answers1

3

The issue may be due to where you're displaying that unicode text.

I pasted those same characters into a text field in my table. Retrieving them with DLookup in the Immediate window causes them to be displayed as question marks because the Immediate window does not display unicode as you wish.

? DLookup("some_text", "tblFoo", "id = 1")
??????

A MsgBox also displays them as question marks.

MsgBox DLookup("some_text", "tblFoo", "id = 1")

However a form text box control does have the capability to handle unicode properly. Binding the text box to the field which contains those characters gives me this ...

Text Box with Unicode characters

A query can also reference unicode characters, and this uses one in its WHERE clause and displays them all correctly when the query is opened in Datasheet View.

SELECT f.id, f.some_text
FROM tblFoo AS f
WHERE (((f.some_text) Like '佛*'));

I suspect this all comes down to how you're trying to use those unicode characters and where you're displaying them.

In a comment, you stated writing those unicode characters to a text file would produce only question marks. However, if you write unicode to a text file (as in the procedure below) and display the file in an editor which is capable of handling unicode correctly, you will see the same characters you see in Datasheet View of the table where they are stored. This screenshot shows Wordpad opened with the file which was created from the code below.

WordPad displaying Unicode text

Dim objFso As Scripting.FileSystemObject
Dim objFile As Scripting.TextStream

Set objFso = New Scripting.FileSystemObject
Set objFile = objFso.OpenTextFile(CurrentProject.Path & _
    Chr(92) & "unicode.txt", ForWriting, True, TristateTrue)
objFile.Write DLookup("some_text", "tblFoo", "id = 1")
objFile.Close
Set objFile = Nothing
Set objFso = Nothing
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • I don't understand what you mean by "call" it. If I display it in the Immediate window or in a message box, it is displayed as question marks. If I display it in something which is unicode-capable (text box or datasheet view), it displays as unicode. Sorry if I'm being dense here. :-( – HansUp Apr 09 '13 at 20:15
  • ah, I guess I should explain what I mean a little more. When I say 'call' I really mean I want to be able to store it in a variable, then the code I've written will build some coldfusion pages that are gonna need that text to be displayed. Perhaps there is a way to convert the characters into something it can read? – Jake Zeitz Apr 09 '13 at 20:18
  • I don't know Coldfusion. I presume you feed it data with a query of your Access db (right?). If so, the query result set contains unicode text as a string of unicode characters (same as my query displayed in Datasheet View). Then the issue is how do you get Coldfusion to display them correctly. I dunno nothin bout that. – HansUp Apr 09 '13 at 20:25
  • Coldfusion is not the problem anyway. It would have the same problem building a txt file, just writing lines. ex: `something.writeline" foreign chars: " & rst![f.some_text]` will show up in that txt file as `foreign chars: ?????`. – Jake Zeitz Apr 09 '13 at 20:31
  • to your edit: yeah coldfusion can read that too, but I can't get VB (debugging using the watch window) to show anything other than `?????`. Does `DLookup` handle that better than writing a `SELECT` statement? Also, keep in mind its an older version of VB and Access – Jake Zeitz Apr 09 '13 at 21:15
  • You're debugging with the watch window, which is also not unicode-capable ... same as the Immediate window itself. Since you see ?????, you're convinced the recordset field does not contain actual unicode despite the fact that writing that very same field value to a file does give you valid unicode. I changed my code to use a recordset (instead of `DLookup`) to retrieve the `some_text` unicode string, wrote that unicode to the file, and saw the same thing in Wordpad as the screen capture in the answer. – HansUp Apr 09 '13 at 21:27
  • I also tested in both Access 2007 and 2003 --- same result in both versions. – HansUp Apr 09 '13 at 21:33
  • Awesome. Makes a little more sense now. – Jake Zeitz Apr 10 '13 at 02:00