Guys,

A VBA problem has been killing me the past two days. I have a Macro Based Model in Excel that has data sets bought into the spreadsheets from Oracle via OLEDB. The code was working completely fine last week and it has not been changed. But now I get an error message that states "Run-Time Error '424': Object Required.

The line where it fails is "oConOracle.Open strConOracle". A connection can't be established with the database!
What is interesting is that via ODBC a connection can be established. The line "oConOracle2.Open strConOracle", executes successfully.

Someone please help.


'Set Connection Parameters
HOST$ = Sheets("Connection Setup").Range("Server")
DBNAME$ = Sheets("Connection Setup").Range("Database")
ORACLE_USER_NAME$ = Sheets("Connection Setup").Range("User")
ORACLE_PASSWORD$ = Sheets("Connection Setup").Range("Password")
ORACLE_PORT$ = Sheets("Connection Setup").Range("Port")
sourcedb = Sheets("Connection Setup").Range("DataSource")


' TEST connections OLE
strConOracle = "Provider=OraOLEDB.Oracle;Data Source=" & sourcedb & "User ID=" & ORACLE_USER_NAME$ & _
";Password=" & ORACLE_PASSWORD$ & ";ChunkSize=1000;FetchSize=100;"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.RecordSet")
oConOracle.Open strConOracle


' Test Connection ODBC
strConOracle2 = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) "
strConOracle2 = strConOracle & "(HOST=" & host & ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
strConOracle2 = strConOracle & "))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"
Set oConOracle2 = CreateObject("ADODB.Connection")
Set oRsOracle2 = CreateObject("ADODB.RecordSet")
oConOracle2.Open strConOracle



Thanks,
Jean