VBA - ADODB connect to remote Oracle db - "Runtime error '-2147418113 (8000ffff)': Catastrophic failure"

[gozu66]

Hi all, any help very much appreciated, pulling my hair out here!!

I am developing an Excel VBA program that I want to connect to a remote Oracle 11g database, run a query, and return the data to Excel.

Connection strings and drivers are all OK as far as I know. (see below)

It was working perfectly for a while yesterday, connecting to db and returning with correct data. Then I saved and closed the workbook, opened it 20 minutes later (no changes made!) and when I ran the macro I got the following error: "Runtime error '-2147418113 (8000ffff)': Catastrophic failure"

any ideas what could be causing the error?
Could it be something on the DB's side?

Thanks

code below:

---

Main VBA module:


Sub ExtractFromOracle(environment As String)

Dim cn As ADODB.connection
Dim recordSet As ADODB.recordSet
Set cn = CreateObject("ADODB.Connection")
Set recordSet = CreateObject("ADODB.recordset")

Dim SQLQuery As String
SQLQuery = "SELECT User_Id, Prof_Id FROM tia_user_profile ORDER BY User_Id ASC"

Dim returnData As Variant
Dim returnedRowsCount As Integer

Dim connectionString As String
connectionString = ReturnConnectionString(environment)


cn.Open (connectionString) ------------> ERROR OCCURS HERE


Set recordSet = cn.Execute(SQLQuery)
returnData = Application.Transpose(recordSet.GetRows)
returnedRowsCount = UBound(returnData)

If Not SheetExist(environment) Then
CreateTab (environment)
End If

Worksheets(environment).Activate
ActiveSheet.Range("A1:B" & returnedRowsCount) = returnData

Set rs = Nothing
Set cn = Nothing

ConsolidateUsers (environment)


End Sub

---

xxxxxx : These are Ok as far as I know, details taken from tnsnames.ora, was working yesterday...

CONNECTION STRING :


"Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxx)(PORT=xxxx)))(CONNECT_DATA=(SID=xxxxx)(SERVER=DEDICATED)));User Id=xxxxxx;Password=xxxxxxx;"

 

[gozu66]

Hey! I fixed the error so I though I would update for anyone with the same issue who finds this thread.

It turns out that the 'Catastrophic Failure' error was due to my Oracle ODBC driver (oraOLEDB.oracle) becoming somehow corrupted, I couldn't even reinstall it properly, the error only occurred on my machine.

In the end I did a system restore to a point from before the issue and all was fixed. Not the most elegant solution but it worked.