Thursday, June 12, 2008

Oracle, Access & ODBC: Problems & Solution

I was using Microsoft Access, in this case Access 97, to connect to an Oracle 10g database, using the ability of Access to set linked tables using an ODBC data source.

For the client, I was using the Oracle Instant Client v10.1.0.4 and setting an environmental variable NLS_LANG.

I was seeing two problems. First, when trying to link to the tables/views on the Oracle server, after selecting the ODBC data source DSN, Access would list all the tables/views on the Oracle server, but It would only display the first character of the name of the tables/views, making it impossible to find the correct table/view.

Second, when I tried setting up the Oracle linked tables in the Access MDB file on another PC, where everything was working ok, and then going back to the original problem PC and opening same MDB file, when opening the Oracle linked tables, all rows and all fields contained #Deleted.

I did a Google and found this Microsoft KnowledgeBase article - Q913070:
#Deleted is displayed in the records when you open a linked ODBC table from an Oracle 10g database in Access 2003, in Access 2002, or in Access 2000

To cut a long story short, I had set the client side environmental variable

but this did not match the setting on the Oracle database, and I was advised
by our Oracle DBA that I should have been using:

Apparently, our Oracle DBA had changed the NLS_CHARACTERSET of the database at some stage in the past.

After I corrected the value of NLS_LANG, everything starting working ok. It seems WE8MSWIN1252 is a binary super set of WE8ISO8859P1.

See these links for further background:

No comments: