Solved

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

Posted on 2011-02-22
8
614 Views
Last Modified: 2016-09-07
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!
0
Comment
Question by:C_Farley
8 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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...
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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.
0
 

Author Comment

by:C_Farley
Comment Utility
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!
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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.)
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Accepted Solution

by:
C_Farley earned 0 total points
Comment Utility
For anyone that's interested, here is our "fix".

Oracle uses a character code identifier in this form... NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 (in our instance). By default, the InstantClient ODBC driver installs with American_America.US7ASCII. To instruct the InstantClient driver to use this language setting, create a system environment variable on the client computer called NLS_LANG and use AMERICAN_AMERICA.WE8ISO8859P1 (or whatever the oracle database you're connecting to uses) as the value.

Of course, only one setting can be used on any given client, but this approach solves our problem.

Oracle NLS_LANG values
0
 

Author Comment

by:C_Farley
Comment Utility
We resolved the problem ouselves.
0
 

Author Closing Comment

by:C_Farley
Comment Utility
We found our own solution.
0
 

Expert Comment

by:toni tonete
Comment Utility
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now