Solved

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

Posted on 2011-02-22
8
637 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 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