Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What Is The Right Provider?
Thanks, I would appreciate that!
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i get in contact with a template provider?? | Excel Discussion (Misc queries) | |||
check for OLE DB provider? | Excel Programming | |||
Provider Issue with Version Change? | Excel Programming | |||
OLE DB Provider | Excel Programming | |||
Excel schema using Jet OLEDB Provider? | Excel Programming |