Forum Stats

  • 3,872,915 Users
  • 2,266,490 Discussions
  • 7,911,382 Comments

Discussions

Excel to Oracle connection Windows 64bit, Excel 32 bit

user574234
user574234 Member Posts: 8
edited Jun 4, 2015 2:41AM in ODBC

I have seen various flavors of this discussion, but am not able to solve my specific problem.

- I am running 64-bit Windows 7 Professional.

- I have Office 32-bit.

- I have both 64-bit Oracle 12, and 32-bit 11g client installed

- My path is

C:\app\**\product\32bit\11.2.0\client_1;C:\app\**\product\12.1.0\dbhome_2\bin;

- I do not have an ORACLE_HOME or TNS_ADMIN variable configured

Now I want to connect Excel to a remote data

base. In the Excel ribbon Data -> Connections, there is a connection of type 'OLE DB Query', with a Connection string

Provider=MSDAORA.1;User ID=**;Data Source=[Host]:1521:[SID];Password=[**]

There is no other database configuration. How can I tell it to connect to the 32bit client? Now when I run this, I get

'ORA-12545: Connect failed because target host or object does not exist'

(I am able to connect to the database using SQLDeveloper)

So next, I modify the 'Connection Properties' - > 'Connection string' to

Provider=MSDAORA.1;User ID=**;Data Source=(DESCRIPTION = (ADDRESS      = (PROTOCOL = TCP)

                                                (HOST     = [Host])

                                                (PORT     = 1521)

                                )

                                (CONNECT_DATA = (SID      = [SID])

                                                (SERVER   = DEDICATED)

                                )

                  )

'ORA-12154: TNS: could not resolve the connect identifier specified'

Does anyone know how to fix this please?

Answers

  • svenkatn-Oracle
    svenkatn-Oracle Member Posts: 74 Employee
    edited Jun 3, 2015 11:39AM

    Hi,

    If you are trying to connect Oracle DB from Excel using Oracle Client, follow the steps as below :

    1. Click on Data in Excel workbook

    2. Choose "From other sources" icon.

    3. In the dropdown menu, choose "From Data Connection Wizard"

    4. Select "Other/Advanced" and click next

    5. Select "Oracle Provider for OLEDB"

    In data link properties provide data source, user id and password to establish DB connection.

    thanks and regards

    Venkat

  • user574234
    user574234 Member Posts: 8
    edited Jun 3, 2015 11:51AM

    When I try to do that, I get error

    'OraOLEDBpus10.dll: The specified module not found'.

    SO is it not able to use the client that I installed?

    Thanks so much!

  • svenkatn-Oracle
    svenkatn-Oracle Member Posts: 74 Employee
    edited Jun 3, 2015 12:02PM

    Hi,

    The message "OraOLEDBpus10.dll: The specified module not found" indicates that the client version is 10g.

    Register the 11g client (using rEGSVR32 utility) and try to establish the connection.


    thanks and regards

    Venkat

  • user574234
    user574234 Member Posts: 8
    edited Jun 3, 2015 12:36PM

    The oracle client I have installed is 11g. I have a 10g oracle installed, but i removed that from the path. OK, I found the rEGSVR32  file in my

    C:\Windows\winsxs\x86_microsoft-windows-regsvr32_31bf3856ad364e35_6.1.7600.16385_none_782d737490d72da3

    directory..hope that is correct.

    It says that I ave to run it with the name of a dll. Which one should I use please?

    Thanks so much!

  • user574234
    user574234 Member Posts: 8
    edited Jun 3, 2015 1:08PM

    Also, I am looking at

    C:\app\**\product\32bit\win64_11gR2_client\client

    There is no bin directory here. And no OraOLEDB11.dll file....

  • user574234
    user574234 Member Posts: 8
    edited Jun 3, 2015 1:39PM

    OK, I managed to do a custom install of Oracle11g driver, and ran the rgsrv32 on the OraOleDB11.dll.

    When I try to make the data connection I still get the

    'OraOLEDBpus10.dll: The specified module not found'.


    Do i have to install Oracle 10g client?

  • svenkatn-Oracle
    svenkatn-Oracle Member Posts: 74 Employee
    edited Jun 4, 2015 2:41AM

    Hi,

    Please perform udl test using 11g client after registering the dll.

    If the connection to database is  successful, use it in your application.

    thanks and regards

    Venkat

This discussion has been closed.