Connection string to connect to remote Oracle 11g

Question

I need to connect to a remote Oracle server from VBA. I do not want to install any additional drivers or software to connect as I need to distribute the Excel to the team.

I tried several connection strings giving errors like SQL_HANDLE_ENV failed. or Oracle client and networking components not found. or when use OraOLEDB.Oracle I get Provider not found.

Connection strings tried so far:

    "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=servicename))); uid=username; pwd=Password;"

    "PROVIDER=MSDAORA.Oracle;DATA SOURCE=hostname;USER ID=username;PASSWORD=Password"

    "PROVIDER=MSDAORA;DATA SOURCE=hostname;USER ID=username;PASSWORD=Password"

    "Driver={Oracle in OraClient11g_home1};Dbq=servicename;Uid=username;Pwd=Password;"

    "Provider=OraOLEDB.Oracle;Data Source=hostname;User Id=username;Password=Password;"

There are some pre defined drivers in windows i suppose. But does it not work for oracle? I use windows 8 Any help greatly appreciated..

I did some more research and came across ODP.net. So the conclusion i think is either use Oracle Instant Client or ODP.net. But i am no sure if any .net Application like ODP.net will work without .Net framework. Please let me know if anyone came across this.

Every version of Oracle I've ever used seems to require the client tools to be installed for you to be able to access the database. — Rory
– Rory, Commented Nov 17, 2015 at 14:37
Yeah, seems so. So the SQL developer should have the connector inside the software itself. As it connects successfully — xGen
– xGen, Commented Nov 18, 2015 at 6:13
If you're using VBA, I think you are going to have to get Oracle clients installed. You can, however, add a .NET plug-in to Excel (VSTO) using the Oracle Managed client, which does NOT have a dependency on a local Oracle client. Without using .NET, I'm not sure how you would do this. — Hambone
– Hambone, Commented Nov 19, 2015 at 8:01
You mean develop an excel plugin using Oracle Client and distribute right? Yeah seems i am running out of other options :( — xGen
– xGen, Commented Dec 7, 2015 at 12:19

Wernfried Domscheit · Accepted Answer · 2015-12-08 10:39:05Z

1

Almost every provider needs an Oracle Client installed. Exceptions are JDBC which is Java and does not work in VBA/Excel and the ODP.NET Mananged Driver. However, ODP.NET Mananged Driver is a .NET assembly and they are not working with COM which is used in VBA/Excel. You would have to write your own wrapper for it.

The .NET framework is part of every Windows installation, there should be no concerns.

btw, did you add Reference for Microsoft ActiveX Data Objects ... to you VBA project?

Looks like you cannot pass by the Oracle Instant Client.

answered Dec 8, 2015 at 10:39

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xGen Over a year ago

OK. So as the .Net framework is preinstalled with windows System.Data.OracleClient is an option. ODP.net requires separate installation i think. I am checking for ways to use .net framework in VBA. Thanks

Wernfried Domscheit Over a year ago

Note, System.Data.OracleClient also requires an Oracle Client to be installed.

PaulG · Accepted Answer · 2015-11-17 15:32:34Z

0

Try this:

Provider=MSDAORA.1;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER0123)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = PRODDB)));User Id=USER_ID;Password=USER_ID_PASSWORD

Obviously you need to replace the server, User ID and Password.

answered Nov 17, 2015 at 15:32

PaulG

1,1977 silver badges9 bronze badges

3 Comments

PaulG Over a year ago

Also, I think you can look in the ora file on you Oracle server which you can get a connection string from it.

xGen Over a year ago

thanks for the reply. Still getting the error "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed."

xGen Over a year ago

The server is not in the same network. I use the hostname like "abc-xyz.i.com" which works from Sql developer UI

Collectives™ on Stack Overflow

Connection string to connect to remote Oracle 11g

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related