Excel Macro to connect to Oracle Database

Question

I have below excel macro to connect to oracle database. But, one executing the code, I get the error message -

"Run-time error '-2147467259 (80004005)': ORA-12541:TNS:no listener"

Please help me resolve this error message.

I have 'Oracle SQL Developer 3.2' installed on my pc.

Sub connectToCISDB()

Dim dbCon As New ADODB.Connection
Dim recordSet As New ADODB.recordSet
Dim strQuery, strCon As String

strQuery = "select * from SAPSR3.eanlh;"

Set dbCon = New ADODB.Connection      
dbCon.Open "Provider=OraOLEDB.Oracle;User ID=xautotext;Password=F7_kxxxxZS;Data Source=ouxxpdxx1"

End Sub

Answer 1

Using the right connectionstring resolved the issue.

Connection String: Driver={Microsoft ODBC for Oracle}; Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=199.199.199.199)(PORT=1523))(CONNECT_DATA=(SID=dbName))); Uid=myUsername;Pwd=myPassword;

No TSN nor DSN required.