Solved

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

Posted on 2011-02-22
8
644 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 34956842
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 48

Expert Comment

by:schwertner
ID: 34959599
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
ID: 34960673
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:rspahitz
ID: 34962499
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
 

Accepted Solution

by:
C_Farley earned 0 total points
ID: 34973075
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
ID: 34973118
We resolved the problem ouselves.
0
 

Author Closing Comment

by:C_Farley
ID: 35005292
We found our own solution.
0
 

Expert Comment

by:toni tonete
ID: 41787953
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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