This content has been marked as final. Show 4 replies
What about NOT using OO4O?
Just wondering, is it possible to connect to Oracle using the Instant Client from Excel VBA?
I know it's easily done with .NET by simply adding a reference to System.Data.OracleClient, placing the few Instant Client DLL's in the executables folder, and setting PATH and TNS_ADMIn vars...but Excel I don't see a way...
Taking another look at this problem...
It looks like it is not specific to OO4O. If I use an ADO connection, with a DSN, I get the same exact results. The DSN is using the Oracle client driver, and connects successfully using the "test" feature. Also, tnsping is successful for all 3 connections on this PC, as well, sqlplus connects just fine. It's just Excel...
TEST I get ORA-12545
DVL I get a successful connection
PRD I get ORA-01034
Very odd, I just can't figure why this one PC would have 3 different issues!
I just realized my ADO connection isn't using a DSN after all...somethings twisted on this PC, just can't figure what. here's the ADO code:
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=DVL;User ID=xxx;Password=xxx;"
'con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=TST;User ID=xxx;Password=xxx;"
'con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=PROD;User ID=xxx;Password=xxx;"
Dim objCmd As New ADODB.Command
objCmd.CommandType = adCmdText
objCmd.CommandText = "select sysdate from dual"
objCmd.ActiveConnection = con
Dim objRes As New ADODB.Recordset
Set objRes = objCmd.Execute