SQL & PL/SQL

Hi,
I really need some help please as I’m new to vbscripting and TNS connections and all this complicated stuff.
I’m trying to make a connection from one of our Oracle servers and extract some basic data via SQL commands but after trying for a couple of days I’m stumped.

Here’s the details and I hope someone can advise me in a simple language on where I’m going wrong.


The server I am trying to connect from is an Essbase server which already has an ODBC connection to the Oracle server setup in the ODBC Datasource Administrator. The driver that I can see in the ODBC Datasource Administrator is ‘Oracle in OraClient10g_home1’ and a ‘SQL Sever’ driver along with some MEARNT OEM drivers.
The tnsnames.ora has the information for the Oracle server and I can tnsping to it successfully.
Browsing round the web I have tried a couple of methods to connect:


"Driver={Microsoft ODBC for Oracle};Dbq=ABCD;Uid=LOGIN;Pwd=PASSWORD;"
I get the error message:
'Provider is not specified and there is no designated default Provider.'


I understand that I don’t have the Microsoft ODBC for Oracle driver installed, not sure if we need it if we have the Oracle in OraClient10g_home1 driver and already have a connection set up. So I tried:


"Driver={ Oracle in OraClient10g_home1 };Dbq=ABCD;Uid=LOGIN;Pwd=PASSWORD;"

Again.. 'Provider is not specified and there is no designated default Provider.'

I also tried another method suggested:


Dim strCon

strCon = "Driver={Oracle in OraClient10g_home1}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=EssbaseServer)(PORT=1592))" & _
         "(CONNECT_DATA=(SERVICE_NAME=ABCD))); uid=LOGIN;pwd=PASSWORD;"

Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs:  Set oRs  = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
oCon.Close

Set oRs = Nothing
Set oCon = Nothing
Same error message again:
Provider is not specified and there is no designated default Provider.

This leads me to believe that maybe I need to have the Microsoft ODBC for Oracle drivers installed on the server. Maybe I need to set my script differently and am totally doing it wrong. Any help please? If you think I need the driver could you please explain why and how it works so I can put forward for my arguement to install it on the server.

Many thanks