Forum Stats

  • 3,836,735 Users
  • 2,262,175 Discussions
  • 7,900,088 Comments

Discussions

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

24

Answers

  • 510663
    510663 Member Posts: 148
    Hello Jam,

    I can connect to the database fine on another computer using the exact same tnsnames.ora file. I am getting the error through ODBC Administrator panel when trying to do a test connection. I'm on a wired network connection.


    When i run %WINDIR%\System32\odbcad32.exe, the new driver is available, I just can't seem to connect. However, When I run %WINDIR%\SysWOW64\odbcad32.exe, the new driver is not an option in the list.

    Best Regards,

    Kelly
  • Hi Jam,

    Yes, you are right, I downloaded Instant Client 11.1 (both 32 and 64 bit) onto my computer.
    So my dir structure looks like:
    C:\Oracle\Instantclient_11_1
    C:\Oracle\Instantclient_11_1.x64

    Of course I have only one TNSNAMES.ora file (under .x64 directory) and set the shell environment pointing to that folder.

    To be able to register both (32 and 64 bit) clients with the appropriate ODBC admin console, and make them available for 32 and 64 bit applications, I had to run the odbc_install.exe from both directories (with admin rights!).

    So now, I can reach database through InstallClient_11_1 - ODBC (32bit) connection from Excel 2007 and I suppose I could connect to database through InstallClient.x86 - ODBC (64bit) connection with Excel 2010 without problem.

    Microsoft ODBC for Oracle has never worked for me.

    Sorry, if I was not clear last time.
  • 764696
    764696 Member Posts: 3
    For us, at this time, we used this method to get it to work for us.

    Uninstalled the 64bit Oracle client. (while we could get our vbscript to connect to oracle via a DSN, the oracle drivers will connect only 1 oracle version different database) (Microsoft ODBC for Oracle is the desired driver since it will connect to various oracle versions)

    Installed 10203_vista_w2k8_x86_production_client (I did a custom install to include odbc and utilities)

    Create a shortcut on the desktop to the 32bit odbc admin. (this is not required, since we prefer to use dsn-less connection strings) (well, thru that admin, we could see that the Microsoft ODBC for Oracle driver was listed)

    For the server, we found these articles that referred to connecting to 32bit drivers.

    http://serverfault.com/questions/98257/need-to-run-an-iis-website-in-32bit-mode-on-a-64bit-windows-server
    http://learn.iis.net/page.aspx/201/32-bit-mode-worker-processes/

    we set on the server:
    aspnet_regiis -enable -i
    cscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 true

    The only downside appears to be:

    http://serverfault.com/questions/38377/running-multiple-32-bit-application-pools-on-64-bit-windows-2008

    I can't find it now, but there was a page that actually suggested to enable 32bit processes on 64bit windows.

    Anyways... presently it appears to work. Our vbscript is using a dsn-less connection string specifying the Microsoft ODBC for Oracle driver to connect to our Oracle 8, 10 and 11 databases.


    (personally, would like to see Oracle step up to the plate and release drivers for current client platforms within a reasonable timeframe of the OS release. Surely, Oracle had access to Server 2008/Windows 7 during the beta period)
  • 767738
    767738 Member Posts: 1
    edited Apr 19, 2010 4:44PM
    Jam The Man's solution worked for me.
    >
    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.


    Thanks man it worked for me.
    My host environment is windows 7 x64 ultimate and I have my oracle 10g in centos installed in VMware.
    Previously I had vista home premium x64 there instantclient 10.2 basic and odbc worked but in windows 7 it wasn't working.
    Thanks again.
  • 776779
    776779 Member Posts: 2
    I have installed Oracle 11g on windows 7. In ODBC administrator I can see the driver for Oracle 11g(64bit) on drivers tab. But when I am trying export a table from MS access 2007(32bit), oracle 11g driver not visible in the display list of create new data source.

    I have uninstalled MSOffice 2007 and installed 64bit version of MS Access data engine from Microsoft and reInstalled Msoffice 2007. But the Oracle driver did n't appear in list in MS Access2007.

    I have created new data source for oracle 11g driver and configured, tested the connection using ODBC adminstrator.The connection is successfull. this time the newly created DSN appeared in MS access ODBC dialogbox. But when I am trying to use it, I am getting followin error message.:

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

    Please help me.

    Thanks in advance
  • SchneiderIS
    SchneiderIS Member Posts: 35
    You sir are a true gentleman and a scholar! This is the answer I have been looking for for two weeks now and it worked perfectly. Thank you so very much.
  • 795909
    795909 Member Posts: 3
    Hi... I am using windows7 home premium 64 bit..... having a problem working with oracle 10g....

    I created a dsn and started working with java..tried to connect to oracle....when ever i run my java program i am getting an error as java.sql.sqlexception ...connection not open.....

    can u plz give a solution for this....
  • Ross.Kerr
    Ross.Kerr Member Posts: 1
    Thank you! That pointer to the 32-bit ODBC driver control panel was just what I needed!
  • Skip_TML
    Skip_TML Member Posts: 72 Blue Ribbon
    I was looking for help getting Excel 2007 ODBC on a Windows 7 64 bit system working into an Oracle On Demand site. I've followed the described steps and once I remembered to use my apps user ID and PW, instead of my VPN user, it worked perfectly. Thank you for your clear explanation.

    S.
  • 809811
    809811 Member Posts: 1
    Worked like a charm! Thanks so much! I was lost trying to find a solution. I followed your instructions EXACTLY.
This discussion has been closed.