How to connect Excell 2017 to Oracle DB to get Data by using VBA code.

hmanhha

Hi All. I Want to use VBA code to get data from Oracle DB The code is here but I fail

Const IPServer = "123.168.YY.XXX" ' Server hosting the Oracle db
Const DBNAME = "NMS" '"DatabaseName"
Const ORACLE_USER_NAME$ = "user"
Const ORACLE_PASSWORD$ = "pass"
Const port = "1521"
Sub ConnectTOOracle()

Dim oRs As ADODB.Recordset
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection

Dim mtxData As Variant
Dim strConOracle As String

strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) "
strConOracle = strConOracle & "(HOST=" & IPServer & ")(PORT=port))(CONNECT\_DATA=(SERVICE\_NAME=" & DBNAME
strConOracle = strConOracle & "))); uid=" & ORACLE\_USER\_NAME & " ;pwd=" & ORACLE\_PASSWORD & ";"




oCon.Open strConOracle



'Cleanup in the end
Set oRs = Nothing
Set oConOracle = Nothing

End Sub

Please help.

Lost User

hmanhha wrote:

but I fail

Yes, you fail to tell us what the problem is. However, looking at your code I get the feeling that ")(PORT=port))(CONNECT_DATA=(SERVICE_NAME=" is not correct. Probably should be: ")(PORT=" & port & "))(CONNECT_DATA=(SERVICE_NAME="

hmanhha

Thanks. I have modify it. But the problem the same. Run-Time error '-2147467259 (80004005': [Microsoft][ODBC Driver manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed. And warning Microsoft ODBC for Oracle. The Oracle (tm) client and networking componets were not found. These components are supplied by Oracle......

Mycroft Holmes

Plug the warning into google and you will find there are some components you need to source from oracle, just like the warning says.

Never underestimate the power of human stupidity RAH