Connect To Oracle Database From Excel VBA

Question

I need to connect to an Oracle database through Excel VBA.

I added the "Micorost ActiveX Data Objects 6.1" library in VBA.

Since I am using 64-bit version of Excel (in a 64-bit Windows 10), I think I cannot use "Microsoft ODBC for Oracle" (which seems to be 32-bit only); that's why I have installed "Oracle InstantClient v21.3" (InstantClient Basic, InstantClient ODBC and InstantClient SDK; all for Windows x64) into "D:\D:\Program Files\Oracle ODBC" and then installed it using the supplied "odbc_install.exe".

I have added that folder to the system PATH and TNS_ADMIN system variable.

My TnsName.ora has the following database definition:

DATAVIEW =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.x.x.x)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = **MyStats**)
    )
  )

I can connect to that database using "PL SQL" and also whenever I add an entry in Windows 10's "ODBC Data Source Administrator (64-bit)", I can connect to the database. Moreover, using the "Data --> Get Data --> From Other Sources --> From ODBC" option in Excel itself, I can see the "MyStats" database in the list and can connect to it.

I am trying to connect to that database using the below code in Excel VBA:

Dim Database_Connection As ADODB.Connection
Dim Record_Set As ADODB.Recordset
Dim Database_Connection_String As String

Set Database_Connection = New ADODB.Connection
Set Record_Set = New ADODB.Recordset

Database_Connection_String = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.x.x.x)(PORT=1521)))(CONNECT_DATA=(SID=MyStats)(SERVER=DEDICATED)));User Id=MyName;Password=MyPass;"

With Database_Connection
    .ConnectionString = Database_Connection_String
    .Open
End With

This runs into different run-time errors (in the ".Open" line).
I tried to supply "Provider=OraOLEDB.Oracle", "Driver=Oracle in Oracle ODBC". I tried TNS-based strings and standalone ones (giving "Data Source=", "Server=", "DBQ=" and ...). I either get run-time error 3706 which is saying the provider is unknown or Runtime-error -2147217805 (80040e73).

I searched a lot of forums and read a lot of examples. They rely on Microsoft ODBC for Oracle (which is what I don't want to use) or get the code to run somehow which I cannot replicate.

Can I give a different "Provider" name in the connection string or driver?

I'd appreciate if you can give me both TNS-based and standalone strings.

If you have created a working User Data Source with ODBC Data Source Administrator the try "DSN=name;UID=user;PWD=password;" — CDP1802
– CDP1802, Commented Dec 1, 2021 at 13:17
Thank you very much; it does work. Just so that I can learn more, I was wondering if you could help me write a TNS-independent Connect String too. — Fereidoun Sadr
– Fereidoun Sadr, Commented Dec 1, 2021 at 13:37
Ok try "Driver={Oracle in Oracle ODBC};SERVER=servername;UID=username;PWD=password;". where Oracle in Oracle ODBC is whatever the driver name is in your DSN. — CDP1802
– CDP1802, Commented Dec 1, 2021 at 17:43
TNS-less connection string should look like this: connectionstrings.com/oracle-provider-for-ole-db-oraoledb/… — Tim Williams
– Tim Williams, Commented Dec 1, 2021 at 17:45

elfsage · Accepted Answer · 2022-02-14 13:08:26Z

0

Had similar problem, but after long research I was able to resolve it with such connection string:

    Dim Cn As New ADODB.Connection
    With Cn
        .ConnectionString = "Driver={Oracle in instantclient_21_3};" & _
                            "DBQ=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service_name>)));UID=<user_name>; PWD=<password>;"

        Call .Open
    End With

Please, change <...> values accordingly.

edited Feb 14, 2022 at 13:08

answered Feb 14, 2022 at 13:01

elfsage

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connect To Oracle Database From Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related