Link to home
Start Free TrialLog in
Avatar of C_Farley
C_FarleyFlag for United States of America

asked on

Access 2007 ODBC character translation from Oracle 11g with 11.1 InstantClient.

When I query Oracle tables from access 2007 using either pass through or linked table objects, Spanish characters display as ? in access. If I use the same ODBC DSN and query the data with Excel 2007, the spanish characters display correctly.

Can anyone tell me how to make access behave like excel in this regard? Both programs are on the same machine (Win XP Pro) and use the same ODBC system DSN.

Thanks very much!
Avatar of rspahitz
rspahitz
Flag of United States of America image

I don't have a specific answer, but where are these characters appearing in Access?  A Textbox on a form?  Maybe you can try to use a RichTextBox with the correct ControlSource to see if it's a problem with the Textbox control...
Avatar of schwertner
Possibly this will help you:

http://download.oracle.com/javase/1.4.2/docs/guide/jdbc/bridge.html

Try to make use of charSet property.
Avatar of C_Farley

ASKER

rspahitz: The characters appear in both a query result set and text box control. The rich text setting seems to have no effect.

schwertner: This Access application at present uses VBA coding for automation and data retrieval with the DoCmd.OpenQuery method or dynamically created SQL pass through query defs.  I'll see if I can adapt the methods in your link with similar ADO objects.

Thank you both for your input!
I've seen that sometimes Word handles special characters when other things don't so maybe Excel is also doing that.

A few more thoughts...
1) Maybe a language pack is needed for Access to show those (although that seems unlikely)
2) If you use the CHR() in a controlsource field to check the ASCII code of the "?", I'm guessing that it will come back with the right value for the Spanish Characters, so then it's simply a matter of finding a good display control for it (which I thought the RichTextBox might do, but apparently not.)
ASKER CERTIFIED SOLUTION
Avatar of C_Farley
C_Farley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We resolved the problem ouselves.
We found our own solution.
Avatar of toni tonete
toni tonete

But... What is "your own solution", i'm having the same issue, and couldn't get ñ characters... changed by ?

WORKS FINE:
Windows 7 32bits
Microsoft ODBC Driver / Oracle 11g driver (in both cases same problem!)
Access 2010 32 bits

DOESN'T WORK:
Windows 7 64 bits
Same drivers
Same access