0

I had been happily using the driver that comes with the oracle client to query oracle databases with the following connection:

Set cn = New ADODB.Connection
cn.Open ("Provider=MSDAORA;" & _
                "User ID=<userID>;" & _
                "Password=<pass>;" & _
                "Data Source=<servername>;" & _
                "Persist Security Info=False")

... that is until I had to start connecting to the same database via python's cx_Oracle. I may not be fully correct, but it appeared I had to download and "install" (adding it to path and oracle_home) the oracle instant client to get it to work. Unfortunately, however, it looks like my vba code stopped working. now i am getting: vba error. I am able to connect to the database using other clients which suggest the database is fine, so something about the instant client must have broken my vba connection.

Can folks who use both cx_Oracle and (excel) vba to connect to oracle databases shed some light on this? What's your set up like, which drivers did you install and how are you setting up your python and vba environment and connection strings to make it work? Thanks!

EDIT: Here's a little additional info. My Excel is 32-bit and python 3.5 is 64-bit. My Oracle client works with my excel so i'm going to have to assume that's 32-bit too. Now the issue I'm running into is that if I install cx_Oracle and the 64-bit instant client, cx_Oracle works but vba stops working. If I remove the instant client, vba comes back to normal but python (naturally) stops being able to oracle.

RAY
  • 6,810
  • 6
  • 40
  • 67
  • One of my colleagues was able to get this to work by not adding the instant client to ORA_HOME (which the installation instruction for cx_Oracle says is needed), and his excel vba works fine. Not for me though. – RAY Apr 25 '16 at 06:44
  • An additional interesting piece of oddity is that if I open my vba/excel sheet without the path variable, it works, and if subsequently close it, add the path and re-open the sheet, it works just fine. However, if I open the sheet initially with the path variable, it doesn't work... – RAY Apr 25 '16 at 06:45

0 Answers0