What Is The Right Provider?
 

I am attempting to connect to an Oracle table.

When I record a macro to connect, it generates the following code.

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=PRG_OPS;UID=YYYYYY;PWD=XXXXXXXX;DBQ=EDSS P.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=1 0;LOB=T;RST=T;GDE=F;FRL=F;BAM=" _
)

I converted this to:
strCnn = "Provider=OraOLEDB.Oracle.1;Password=XXXXXXXXX;Use r ID=YYYYYYY;Data
Server=EDSSP.WORLD;Persist Security Info=True"

I am guessing that perhaps the "OraOLEDB.Oracle.1". What and how do I know
what to place in this field.

I am getting the following error:

-2147467259: ORA-12560: TNS:protocol adapter error

What Is The Right Provider?
 

Jim,

Are you able to ping the Oracle DB from the command prompt?

Type: "tnsping EDSSP.WORLD"

in a command window. If not successful then maybe you have a problem with
your tnsnames.ora

Tim

"Jim Heavey" wrote in message
...
I am attempting to connect to an Oracle table.

When I record a macro to connect, it generates the following code.

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=PRG_OPS;UID=YYYYYY;PWD=XXXXXXXX;DBQ=EDSS P.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=1 0;LOB=T;RST=T;GDE=F;FRL=F;BAM="
_
)

I converted this to:
strCnn = "Provider=OraOLEDB.Oracle.1;Password=XXXXXXXXX;Use r
ID=YYYYYYY;Data
Server=EDSSP.WORLD;Persist Security Info=True"

I am guessing that perhaps the "OraOLEDB.Oracle.1". What and how do I
know
what to place in this field.

I am getting the following error:

-2147467259: ORA-12560: TNS:protocol adapter error

What Is The Right Provider?
 

Thanks for your response. I am perfectly able to connect to the table when I
use the macro recorder, so I am finding it difficult to thing it has anything
to do with TNSNames. But I entered the command you suggested and I got the
following:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)
(HOST = XXX.XX.XX.XX)(PORT = YYYY))) (CONNECT_DATA = (SERVICE_NAME = EBWP)))
OK (430 msec)

This seems to suggest that it worked just fine... doesn't?

What Is The Right Provider?
 

I can send you a sample of what works for me when I get to work tomorrow...

Tim



"Jim Heavey" wrote in message
...
Thanks for your response. I am perfectly able to connect to the table
when I
use the macro recorder, so I am finding it difficult to thing it has
anything
to do with TNSNames. But I entered the command you suggested and I got
the
following:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL
=
TCP)
(HOST = XXX.XX.XX.XX)(PORT = YYYY))) (CONNECT_DATA = (SERVICE_NAME =
EBWP)))
OK (430 msec)

This seems to suggest that it worked just fine... doesn't?

What Is The Right Provider?
 

Thanks, I would appreciate that!

What Is The Right Provider?
 

Jim,

Either of these work for me:

sConnString = "Provider=MSDAORA;Password=" & Password & _
";User Id=" & User & _
";Data Source=" & Instance


sConnString = "Provider=OraOLEDB.Oracle;User Id =" & User & _
";Password=" & Password & _
";Data Source=" & Instance


Tim.

--
Tim Williams
Palo Alto, CA


"Jim Heavey" wrote in message
...
Thanks, I would appreciate that!