Windows 7 - ODBC for Office 2010 64 bit - MS ODBC Driver for Oracle

I am trying to link a table from an oracle rac 10.2.0.4 64 bit install.
I have windows 7 with the oracle 64 bit client.
in odbc i can create a datasource using the oracle driver
and when i try to link a table i get an error :
Reserved error (-7732); there is no message for this error.

I would like to use the Microsoft ODBC for Oracle Driver but it doesnt exist on my computer.
the old one on windows xp used file MSORCL32.DLL is there a MSORCL64.DLL now?
Anyone have any help?

Jeff

It would be nice if someone could answer this question. I am having the same issue.

Regards,

Kelly

here is currently no Oracle software supported on Win7.

For Windows 7 support information refer to the following notes on My Oracle Support:

Statement of Direction: Oracle Database 10g Release 2 (10.2.0.5) Client on Microsoft Windows 7 and Microsoft Windows Server 2008 R2
Read My Oracle Support Note 1061272.1 to learn about Oracle Database Client 10.2.0.5 planned support for Windows 7 and Windows Server 2008 R2.

Statement of Direction: Oracle Database 11g Release 2 on Microsoft Windows 7 and Microsoft Windows Server 2008 R2
Read My Oracle Support Note 867040.1 to learn about Oracle Database's planned support for Windows 7 and Windows Server 2008 R2.

32 bit software requires 32 bit odbc driver and 32 bit client.
64 bit software requires 64 bit odbc driver and 64 bit client.
Office2010 comes in both 32 bit and 64 bit flavors as I understand it, so you'll need the right version of odbc/client depending on which you have installed.
Microsoft only makes a 32 bit ODBC driver for Oracle.

Hope it helps,
Greg

Edited by: gdarling on Mar 2, 2010 11:13 AM

Here's the solution.

My operating system is Windows 7 64-bit, using Office 2010 64-bit. Trying to connect to an ODBC data source through Excel. The data source is an Oracle9i 32-bit database on a Windows Server 2003 32-bit server. However, the database version or server operating system is arbitrary; it could be an 8i, 9i, 10g, or even an 11g 32-bit or 64-bit database on a UNIX or Windows server. If you're using Windows 7 64-bit and you want to connect to an ODBC data source through Excel to an Oracle database, you must use Office 2010 64-bit. You cannot use Office 2003 or 2007, for example.

Follow these steps on your Windows 7 64-bit PC to connect to your ODBC data source.

1. Through Oracle's web site, go to Instant Client Downloads for Microsoft Windows (x64). Currently, the address to that page is:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winx64soft.html

2. Download the two following files, found under the version 11.1.0.7.0 heading:
Instant Client Package - Basic (instantclient-basic-win-x86-64-11.1.0.7.0.zip)
Instant Client Package - ODBC (instantclient-odbc-win-x86-64-11.1.0.7.0.zip)

3. Extract both zip files to the same directory (such as c:\oracle\instantclient_11_1).

4. Launch the Command Prompt (DOS command window) by running it as administrator. Go to Start > All Programs > Accessories, then right click Command Prompt and select Run as Administrator.

5. From within the Command Prompt, navigate to c:\oracle\instantclient_11_1, and run odbc_install.exe. You should receive a message saying Oracle ODBC Driver is installed successfully. The name of the new ODBC driver that was just installed is Oracle in instantclient11_1 -- use this driver when creating your ODBC connection.

6. Create a new system environment variable. Set the value of the variable name as TNS_ADMIN, and the value of the variable path as the directory that contains the tnsnames.ora and sqlnet.ora files. On my PC, I have another Oracle client already installed which has its own tnsnames.ora and sqlnet.ora files. Therefore, I set the value of variable path as c:\orant\net80\admin (the directory that contains my tnsnames.ora and sqlnet.ora files). If you don't have another Oracle client installed on your PC, create a new directory such as c:\oracle\instantclient_11_1\network\admin, and place the tnsnames.ora and sqlnet.ora files in that directory. Then, set c:\oracle\instantclient_11_1\network\admin as the value for the variable path.

7. To create the ODBC connection, go Start > Control Panel > Administrative Tools > Data Sources (ODBC). Alternatively, you can create the connection right from within Excel.

Hi,

I think this link makes these excellent details more clear:http://robertoschiabel.wordpress.com/2008/02/28/windows-x64-32bit-odbc-vs-64bit-odbc/

This describes how to verify 32/64 bit ODBC connections (because they are treated separately) on Win7 64-bit.

I downloaded both 32/64 bit Oracle clients + their ODBC driver to my computer, and I was able to register both ODBC driver successfully based on your explanation.

So now, I can use my Office 2007 with the 32bit Instantclient ODBC package, and probably I could use Office 2010 with the 64bit one (I don't have Office 2010)

So don't forget. Setup 32bit ODBC connections with %WINDIR%\SysWOW64\odbcad32.exe
and use %WINDIR%\System32\odbcad32.exe for the 64bit driver.

Cheers

Hello,

I have to say I am pleased people are trying to help; however, I can not get this solution to work.

I followed Jam The Man's instructions completely. I am getting the following error:
TNS:lost contact

I am running Windows 7 64 bit OS.

user575710,

When you say, So don't forget. Setup 32bit ODBC connections with %WINDIR%\SysWOW64\odbcad32.exe
and use %WINDIR%\System32\odbcad32.exe for the 64bit driver. I simply don't know what you mean...

Any help is appreciated.

Kelly

elly,
I've implemented that solution for a number of clients of ours, so I know those steps work. The error you're getting, TNS:lost contact, may be indicative of another problem. What is the database (or data source) that you're trying to connect to? Can you connect to it from another machine, for example? Are you getting that error through the ODBC Administrator panel when trying to do a test connection, or were you getting that error through Excel? What is your connection to the network, I've seen issues when the client PC was on a wireless network trying to connect to an Oracle db?

The other question you had. On Windows 7 64-bit, there are two ODBC Administrator control panels. There's a 32-bit one, and a 64-bit one. 32-bit apps (like Office 2007) cannot use the 64-bit ODBC drivers. 64-bit apps (like Office 2010 64-bit) cannot use 32-bit ODBC drivers. So in Windows 7 64-bit, Microsoft created two ODBC Administrator consoles for setting up the appropriate drivers.

32-bit ODBC connections are setup in %WINDIR%\SysWOW64\odbcad32.exe - this you can launch from the Command Prompt
64-bit ODBC connections are setup in %WINDIR%\System32\odbcad32.exe - this is what launches when you go to Control Panel > Administrative Tools > Data Sources (ODBC)

user575710,
I tried using the standard 32-bit ODBC driver "Microsoft ODBC for Oracle" when I attempted to try to connect to an ODBC source through Excel 2007 (32-bit) or Office 2010 (32-bit), yet it was failing to connect (on Windows XP 32-bit or Windows 7 32-bit, that driver works). I did create the DSN through %WINDIR%\SysWOW64\odbcad32.exe. Since it didn't work, that lead me to believe that using a 32-bit ODBC driver on Windows 7 64-bit to connect to your data source through Office 2007 is not possible. Just to be clear, the 32-bit ODBC driver you used to connect through Office 2007 was the one installed by using the 32-bit Instant Client Basic and ODBC packages? If so, I'll give it a try and let you know.

Hi,

Thanks for steps. I am trying to create ODBC connection for windows 7 64 bit as per the steps you described but i am getting the error

"Unable to connect SQLState=08004 ORA-12154:TNS:could not resolve the connect identifier specified".

TNS Service Name in ODBC setup shows the junk character instead of my service name. May be I missed some configuration.

what i am doing wrong.

Thanks in advance.

Sumit Agarwal

https://forums.oracle.com/forums/thread.jspa?threadID=1010383&tstart=0