Oracle Connection Problem

[Jim Heavey]

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(Array( _

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

I converted this to:
strCnn = "Provider=OraOLEDB.Oracle.1;Password=XXXXXXXXX;User ID=YYYYYYY;
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: TNSrotocol adapter error

The system generated macro connects to the Database just fine.
TNSPing works just fine, so there is something wrong with how I have
constructed my connection string.

 

[Robert ap Rhys]

I am attempting to connect to an Oracle table.

The system generated macro connects to the Database just fine.
TNSPing works just fine, so there is something wrong with how I have
constructed my connection string.

Hi Jim,

A good way of creating and testing connection strings is to right-click on
your desktop and choose New > Text Document. Rename the new document to
ConnString.udl (or anything dot udl, really). Widows will give you the
patronising warning about changing the filename extension. Click Yes to
confirm the change.
The new file will now have a new icon. Double-click the icon and the Data
Link Properties dialog will appear. Set up your database connection in there
and hit the Test Connection button to ensure that the connection works OK.

Click OK to close the dialog. Now open the .udl file with notepad (or a
proper text editor ;-) and there you will find your connection string.

HTH

Robert

 

[Tim Williams]

Jim,

See my examples in your previous thread.

Tim

--
Tim Williams
Palo Alto, CA

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(Array( _

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