Solved

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

Posted on 2011-02-22
8
628 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 47

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

803 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