Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-22
8
Medium Priority
?
680 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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