Trying to connect to an Oracle DB Server using VBA

Question

As the title states I'm trying to connect to an oracle DB for a vba script.

I was using this article here on how to do it: https://blogs.perficient.com/2015/12/17/connectcompare-databases-excel-macro/

I just have very basic code for connecting and I'm getting an error that says "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

My code is:

Private Sub CommandButton1_Click()
    Dim dbConnection As ADODB.Connection
    Dim recordSet As ADODB.recordSet
    Set dbConnection = New ADODB.Connection
    Set recordSet = New ADODB.recordSet
    conString = "Driver = {Microsoft ODBC for Oracle};DBQ=####;Uid=<####>;Pwd=<####>;"
    dbConnection.Open (conString)

End Sub

One thing I am not sure on is what exactly is DBQ is. I have set DBQ to the name of the database I'm interested in. Not sure if I'm setting that parameter properly.

Thank you and all help is appreciated it.

what version of oracle? Do you have an Oracle client installed? — kevinskio
– kevinskio, Commented Mar 13, 2020 at 14:35
Java(TM) Platform 1.8.0_221 Oracle IDE 19.4.0.354.1759 Microsoft ODBC for Oracle 10.00.17763.01 Hope that helps, or did you mean something else? — Bob Stoops
– Bob Stoops, Commented Mar 13, 2020 at 14:38
Instead of DBQ try using Server. See this link for details. — Brian M Stafford
– Brian M Stafford, Commented Mar 13, 2020 at 14:47
Hi! sorry about that, I am using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production — Bob Stoops
– Bob Stoops, Commented Mar 13, 2020 at 14:48

Wernfried Domscheit · Accepted Answer · 2020-03-13 15:18:26Z

First question: Did you install an Oracle Client on your machine?

According to your connection string you use the Microsoft ODBC for Oracle driver. Connection string must be like this:

Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

Note, the connection string is slightly different to the Oracle ODBC driver, see ODBC Driver Microsoft vs. ODBC Driver Oracle

Second question: Is your Excel 32-bit or 64-bit?

The Oracle Client and the ODBC driver must be the same architecture (i.e. 32-bit vs. 64-bit) as your Excel. The ancient Microsoft ODBC for Oracle driver exist only for 32-bit. If your Excel is 64-bit then you have to use the Oracle ODBC driver.

Another note, the default Oracle Instant client does not include the ODBC driver, you have to download and install the "ODBC Package", from Oracle Instant Client Downloads

kevinskio · Accepted Answer · 2020-03-13 14:51:24Z

0

There is a comment above to use this format which I believe is correct "DSN={MyDataSource};UID={MyUserID};PWD={MyPassword};SERVER={MyOracleServer}"

From the Microsoft documentation and here

answered Mar 13, 2020 at 14:51

kevinskio

4,5711 gold badge27 silver badges42 bronze badges

2 Comments

Bob Stoops Over a year ago

Yo dumb question, but how would I find the "DSN" and "SERVER". I have SQL Developer open with the DB I'm interested in, but not sure where exactly I'd find that info.

Wernfried Domscheit Over a year ago

DSN is used when you have defined the DSN in your "ODBC Administrator" tool. SERVER is used to name the database directly (which you also enter SQL Developer).

Collectives™ on Stack Overflow

Trying to connect to an Oracle DB Server using VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related