0

I am currently writing a excel macro that connects Oracle server and encountering the following failure.

'run-time error '-2147418113 (8000ffff)' - Catastrophic Failure'

The code that I wrote is as following:

    StrConn = "Provider=ORAOLEDB.Oracle;Data Source=xxxxxxxx:xxxx/xxxx;
               User Id=xxxxxxx;Password=xxxxxxxxx;"

    Set Conn = CreateObject("ADODB.Connection")
    Set Rs = CreateObject("ADODB.Recordset")

    Conn.CommandTimeout = 1000
    Conn.cursorlocation = 3 
    Conn.Provider = "OraOLEDB.Oracle"
    Conn.Open StrConn

The error occurs on Conn.Open StrConn

And yes I have installed Oracle OLE DB drivers on my computer.

I have read a lot of solutions on similar problems but non worked for me. I have tried adding prompt property, changing cursorlocation, reinstalling the drivers, changing vba reference, but it all results in the catastrophic failure I am experiencing.

There is another weird part to the problem. As my senior tried to help me to solve the problem, I sent the excel file to him to another computer (Lenovo), installing the oracle drivers the same as I did on my computer (HP). Yet, the code is working fine on the Lenovo computer. Thus, I did a little experiment and tried installing oracle drivers and opening the excel file on another HP computer (same model as my computer) and the same error occurred.

I have spent days trying to find solutions on the internet. Can somebody help me...

braX
  • 11,506
  • 5
  • 20
  • 33
satheia
  • 1
  • 1
    Did you install 32-bit or 64-bit Oracle OLE DB client libs? Are you running 32-bit or 64-bit Office? – Dai Jul 22 '22 at 03:15
  • see if this helps https://stackoverflow.com/questions/61188518/how-to-connect-to-oracle-database-using-vbscript-and-uft – psaraj12 Jul 22 '22 at 04:55
  • You can install only one version of OleDb driver (i.e.one each for 32 bit and 64 bit) Perhaps you mixed up something. – Wernfried Domscheit Jul 22 '22 at 05:54
  • I am running 32bit Office I have installed ODAC 21c (both 32 and 64 bit) from https://www.oracle.com/database/technologies/net-downloads.html Added symbolic link from driver folder to system 32 and sysWOW64 and added environmental path to system32/odac and system32/odac/bin – satheia Jul 22 '22 at 07:57
  • _"Added symbolic link from driver folder to system 32 and sysWOW64 and added environmental path to system32/odac and system32/odac/bin"_ - all of that sounds unusual and suspicious to me. None of that is necessary for COM-based registration (which OLE-DB is built on top-of), and that's exactly the kind of thing that can break COM... – Dai Jul 22 '22 at 09:37
  • @Dai, it may come from my solution at https://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100 You are right, symbolic links are not required for OleDB driver, but it should not break anything. – Wernfried Domscheit Jul 22 '22 at 21:09
  • You may try my [Oracle Connection Tester](https://github.com/Wernfried/connection-tester) or use the [Process Monitor](https://learn.microsoft.com/en-us/sysinternals/downloads/procmon) to see what is loaded and where it fails. – Wernfried Domscheit Jul 22 '22 at 21:12
  • Maybe you need to do a proper uninstall, see http://stackoverflow.com/questions/8450726/how-to-uninstall-completely-remove-oracle-11g-client – Wernfried Domscheit Jul 22 '22 at 21:15

0 Answers0