So you have Microsoft Office 2010 64 bits a Windows 7 64 bits system, and you are trying to connect to an ODBC data source through Excel. The data source is an Oracle server.

Here’s the solution.

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.

Through Oracle’s web site, go to Instant Client Downloads for Microsoft Windows (x64). Currently, the address to that page is: www.oracle.com/technetwork/database/features/instant-client/

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)

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

Copy your previous files TNSNAMES.ORA end SQLNET.ORA in this new folder.

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.

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.

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.

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

Then you can have another problem: Your users already have EXCEL files with a driver named for example : « Microsoft ODBC for Oracle ». »ODBC_Connection_in_EXCEL »

Oracle in instantclient10_2 is set by odbc_install.exe and can be altered if you change the registry:

In

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers

, change the name of  « Oracle in instantclient10_2 » to whatever you want.

Rename the, « Oracle in instantclient10_2 » key in

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI

to the same name you used above.

Now your EXCEL 64Bits should connect well to Oracle.

Print Friendly, PDF & Email

Leave a Reply

You have to agree to the comment policy.

Blue Captcha Image
Refresh

*

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.