How to connect Excel to Oracle database?

Question

I am trying to connect Excel to my Oracle database using VBA.

Sub dbConnect()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset

    strCon = "Driver={Microsoft ODBC for Oracle}; " & _
    "CONNECTSTRING=(description=(address=(protocol=tcp)(host=mydb.domain.com)(port=1522))(connect_data=(sid=mydb))); uid=user; pwd=pw;"
    con.Open (strCon)

End Sub

I get an error.

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I know from other questions and sources that the problem is most likely using the wrong version of the DSN.

However, I don't understand what I need to do to fix it.

My Windows is 64-bit, when I open the ODBC Data Source Administrator I see the following (among others):

Name: Excel Files, Platform: 64-bit, Driver: Microsoft Excel Driver

User DSN
Name: mydb, Platform: 32-bit, Driver: oracle in ORA121020_x86

under System DSN

What can I do to fix it? How do the connection string and the DNSs relate to each other? Should I change the version of one of the DNS and if yes how do I do that?

Edit: The connection string is copied from the db connection in Oracel SQL Developer where I can access the db.

64 bit, however, I have already solved the problem and answered my own question — krise, Jan 03 '20 at 16:12
Exactly, driver `Microsoft ODBC for Oracle` exists only for 32-bit, thus you cannot use it for your Excel which is 64-bit. — Wernfried Domscheit, Jan 03 '20 at 16:24

score 1 · Accepted Answer · answered Jan 03 '20 at 16:12

Ok I managed to fix it. My excel is 64 bit so in order to connect to the db, I had to create a System DNS for my connection that is also 64 bit. In order to do that, I had to go to the /Windows/system32 folder, choose the file odbcad32 and under system DNS add a new DNS with a 64 bit driver I had to download. Lets say I named that DNS abc

I then also changed the connection string that you can see in the above code to

strCon = "Data Source=abc;User=user;Password=pw"

According to this the connection string I used in the post above does not need a DNS, so I don't know why it didn't work, however after creating a new DNS as just described I switched to the new connection string that specifies a DNS.

And voila, after only several hours I was able to connect to my db.

Quick sidenote: As mentioned above, I edited the obcad32 file in /Windows/system32. This is the file for 64 bit DNS. There is a file with the exact same name in the folder /Windows/SysWOW64 that manages the 32 bit DNS, so if you have a similar problem as I had, pay attention to which file you edit.

How to connect Excel to Oracle database?

1 Answers1