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

gozu66

New Member
Joined
Dec 6, 2017
Messages
2
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;"
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top