Oracle Connection Problem

J

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: TNS:protocol 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.
 
R

Robert ap Rhys

Jim Heavey said:
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
 
T

Tim Williams

Jim,

See my examples in your previous thread.

Tim

--
Tim Williams
Palo Alto, CA


Jim Heavey said:
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=" _
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top