Windows 7 64 bit, Access 2010 32 bit, Oracle 10G client 32 bit, Oracle database 10.2.0.4 64 bit

coldrunner

I am trying to connect to an Oracle 10.2.0.4 database from Windows 7 OS - 64 bit using
Access 2010 32 bit and Oracle 32 bit client version 10.2.0.1 installed on PC.
My user requires a 64 bit Windows 7 OS for other needs.

Oracle ODBC driver is 32 bit from Oracle 32 bit client.

In Windows 7 OS 64 bit, ODBC DSN setup software is located in:

C:\Windows\system32\odbcad32.exe for 64 bit drivers - all DSN types setup and
C:\Windows\syswow64\odbcad32.exe for 32 bit drivers - USER DSN setup

When I run the ODBC Data Source Administrator, I see the Version number is 6.1.7601.17632

I setup a User DSN using the 32 bit odbcad32.exe with a 32 bit ODBC driver.
Our Oracle TNSnames.ora file has our standard entries which have worked for years
(Windows 7 OS 32 bit and everything 32 bit has connected for years too)

So when I configure an ODBC User DSN and leave all the default settings and press the TEST Connection button, it says it works.

But when I open the existng Access 2010 database (which does connect to Oracle in a Windows 7 32 bit PC environment),

1. I go to Access External Data ribbon tab / Link to the Data Source by creating a linked table / press OK
2. I select the Machine Data source = to the USER DSN name / press OK
3. The Oracle ODBC Driver Connect Window opens with Service Name = USER DSN and User Name and it wants a Password
4. I enter the correct password and I get the following error message:

ODBC call failed
[Oracle][ODBC]{Ora]ORA-12154: TNS: could not resolve the connect identifier specified
(#12154) [Microsoft][ODBC Driver manager] Driver's SQLSetConnectAttr failed IM006 0
         [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (#0)

I have tried a quite a number of fixes:

I have gone into the PC registry and cleaned out most or all off the Oracle client keys and
reinstalled the Oracle 32 bit 10g client. It didn't fix the error.

I read about the Oracle instantclient-basic-win32-10.2.0.4.zip with ODBC modules but I have Windows 7 64 bit.

Anyone experienced a problem like this and have a fix?

coldrunner

Thank you for your reply.

I did not get to try it because after I entered my question, I did a lot more internet Googling.

I found this small comment:

“There's a known issue where you get a 12154 error message running 32 bit software on a 64 bit OS using client 10201, and

It should be resolved by patching the client to 10204, which you do by applying the 10204 database patch to it.”

I searched around for the 10.2.0.4 patch for 10.2.0.1 Oracle 32 bit client.

When I contacted my Oracle DBA to ask if he had that patch, he said that I should try the 11g 32 bit client on the Win 7 64 bit PC.

We have a 10.2.0.4 Oracle database but he said the 11g client should work fine.

I tried it and it solved the problem!

With so many settings, configurations and permissions involved in ODBC, I am inclined to think that I missed a setting but

it was a bug in the 32 bit client 10.2.0.1.

Solved.

Sdhamoth-Oracle

Try starting MS-ACCESS with "Run as administrator" option. That may fix your issue.

coldrunner

Thank you for your reply.

I did not get to try it because after I entered my question, I did a lot more internet Googling.

I found this small comment:

“There's a known issue where you get a 12154 error message running 32 bit software on a 64 bit OS using client 10201, and

It should be resolved by patching the client to 10204, which you do by applying the 10204 database patch to it.”

I searched around for the 10.2.0.4 patch for 10.2.0.1 Oracle 32 bit client.

When I contacted my Oracle DBA to ask if he had that patch, he said that I should try the 11g 32 bit client on the Win 7 64 bit PC.

We have a 10.2.0.4 Oracle database but he said the 11g client should work fine.

I tried it and it solved the problem!

With so many settings, configurations and permissions involved in ODBC, I am inclined to think that I missed a setting but

it was a bug in the 32 bit client 10.2.0.1.

Solved.