Connect To Random Oracle Database From Excel

[agmorgan]

How do I connect to an Oracle database from Excel to run some queries when I don't have access to tns or ODBC?

I currently have a series of SQL queries that I run on the (Oracle) database of a commercial system in multiple companies to do some analysis checks.
At the moment I connect using a java application (SquirrelSQL) and JDBC with the oracle thin connection.
e.g.

jdbc:oracle:thin:@<hostname>:<port>:<oracle-sid>
jdbc:oracle:thin:@localhost:1521:XE

With java, all I need from the client is the host, port, s-id, username and password.
I have the Oracle driver file (ojdbc6.jar) with me and SquirrelSQL as a portable application running from my USB stick.

I run each query then copy the output and paste it into Excel to present it nicely to the client.

It would be nicer if I had all the SQL stored in some VBA, then just updated the connection details on the first sheet and pressed a button to populate the sheets automatically.

However, I can't seem to work out how to connect to oracle using VBA.
When I try stuff like this:
http://www.vbforums.com/showpost.php...31&postcount=2

I get the error:
"data source name not found and no default driver specified"

Is there a dll that I need to load that I can take around with my sheet (like the ojdbc.jar)?

[RhinoBull]

Oracle Client has to be installed so some kind of drivers will be present - with that sadi odbc entryu can be created on-the-fly if you need one.
Also, In Excel you can reference ADO library so you can utilize OLEDB provider - this is much better option.
TNS less connection string must contain everythig you would have in tns file:.
For more detailed syntax for to: http://www.connectionstrings.com/oracle and lookup "TNS-less connection string" on that page.

[agmorgan]

I didn't understand your second statement.
Are you saying that I can do it with just ADO and OLEDB and no extra drivers?

The way I understood it was the same as your first statement, that an Oracle Client had to be installed too.
I can't really rely on this as I won't know what PC I will be at.
I read about the Oracle Instant Client but that still appears to need registry and environment variables to work.

Am I missing something?
Even if there is just a single driver file (like in java) would it be possible to make it 'portable'?
e.g. Load it the driver from a file from Excel without needing Admin rights to install it etc?

The TNS-less connection string is:

Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)));User Id=myUsername;Password=myPassword;

[Hack]

I can't really rely on this as I won't know what PC I will be at

Have you considered simply getting your own laptop, loading it up with whatever you need, and just plugging yourself into their networks so you have access to their databases? Would that work for you?

[agmorgan]

Thats the unfortunate part, very often banks won't allow external laptops on their networks.

I'm starting to think it might be easier to do it in the reverse direction - connect to the database and do the queries with java then use a java library to populate the spreadsheets with the data.

It doesn't feel as slick though

[RhinoBull]

I didn't understand your second statement.
Are you saying that I can do it with just ADO and OLEDB and no extra drivers?

No, you'll still need an Oracle Client installed - OLEDB is just much better option than ODBC that you mentioned.
If you get new pc every time you come to work then you'd have to install OC first - as far as I am aware there is no way around that.