Connect to an Oracle db with ADODB using excel VBA

Question

I'm using this connection string pattern with ADODB

Set oConn = New ADODB.Connection
oConn.ConnectionString = "User ID=USERNAME;Password=PW;Data Source=xxxxxxx.db.yyyy.com:port;Provider=OraOLEDB.Oracle; "
oConn.ConnectionTimeout = 30
oConn.Open
rs.CursorType = adOpenForwardOnly

and when I open the connection I'll get a run time error

ORA-12504: TNS:listener wa not given the SERVICE_NAME in CONNECT_DATA

I have also tried using this connection string

"ODBC;DRIVER={Oracle in OraClient11g_home2};" & _
"DBQ=" & inputHost & ";UID=" & inputUser & ";PWD=" & inputPassword & ";" & _
"HOST=" & inputHost & ";PORT=1521;DB=" & inputHost & ";" & _
"DefaultIsolationLevel=READUNCOMMITTED"

and I get the run time error

[Microsoft][ODBC Driver Manager] Data source name not found an no default driver specified

In my limited experience this means that you need to edit the tnsnames.ora file to include or fix this connection. — Doug Glancy
– Doug Glancy, Commented Jul 20, 2016 at 0:47

Wernfried Domscheit · Accepted Answer · 2016-07-20 08:11:44Z

1

I think Data Source=xxxxxxx.db.yyyy.com:port is the problem. You should provide the TNS alias as defined in file tnsnames.ora instead of "ServerName.Domain:Port".

I do not know if OLEDB provider supports Easy Connect naming method. Did you specify like NAMES.DIRECTORY_PATH=(tnsnames, ezconnect) in your sqlnet.ora file?

answered Jul 20, 2016 at 8:11

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ejj Over a year ago

So what kind of connection string patter should I use?

ejj Over a year ago

Ended up just having to use ConnectionString = _ "DRIVER={Oracle in OraClient11g_home1};" & _ "DBQ={{from tnsnames.ora}};UID=xxxxx;PWD=xxxx;"

Collectives™ on Stack Overflow

Connect to an Oracle db with ADODB using excel VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related