Oracle Connection String in VBA



Database | Excel Macro | Excel Macro Tutorial

Dear Friends,

Using Excel Macros (VBA) you can connect to Databases like SQL, Oracle or Access DB. In this Article, I will teach you, how you can do a connection with a Oracle Server.
We can do connection with Oracle either by giving SID (Oracle System ID) or Service Name. Whichever is available for the connection, we can use them to connect to the Oracle Database.

Prerequisite

Before running the below code, you need to Add reference for ADODB Connection. If you do not know how to add references for ADODB connection in Excel workbook, follow below steps.

Step to Add ADODB Connection References in Excel

1. Go to VB Editor Screen (Alt+F11)
2. Tools –> References…
3. From the List of Available References Select “Microsoft ActiveX Data Objects 2.0 Library” You can select 2.0 Version or any other higher version of this reference.
4. Click OK

Connection String with SID
Connection String with Service(DB Name)

Oracle Connection String with SID:

Use the below Code for connecting to the Oracle Database.


Sub Ora_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'---- Replace below highlighted names with the corresponding values
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=Your Host Name)(PORT=Port Number))" & _
"(CONNECT_DATA=(SID=SID of your Database))); uid=User ID; pwd=Password;"
'---  Open   the above connection string.
con.Open (strCon)
'---  Now connection is open and you can use queries to execute them. 
'---  It will be open till you close the connection
End Sub

Oracle Connection String with Service:

Use the below Code for connecting to the Oracle Database.



Sub Ora_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'---  Replace below highlighted names with the corresponding values
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=Your Host Name)(PORT=Enter Port Number))" & _
"(CONNECT_DATA=(SERVICE_NAME=database))); uid=Enter User ID; pwd=Enter Password;"
'---  Open the above connection string.
con.Open (strCon)
'---  Now connection is open and you can use queries to execute them.
'---  It will be open till you close the connection
End Sub

Read this Also:

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

Feb 3, 2024

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

Kannan on August 22, 2012 at 3:26 pm

is there any free vba obfuscator to protect my modules and vba project?
Reply
- Vishwamitra Mishra on August 26, 2012 at 10:47 am
  
  Hi Kannan,
  Sorry i do not know any tool as such.
  Thanks,
  Vish
  Reply
  - Kannan on August 27, 2012 at 9:56 am
    
    Is there a way to embed a ".xlsm" excel file to blogspot so that the users can actually do the calculation on the blog without me sharing either the excel or code? Is it feasible to do this? Does excel have this capabilities?
    Reply
    - Ricardo on June 13, 2015 at 4:16 am
      
      Hi,
      
      I know you can embed spreadsheets developed at OneDrive (the Microsoft online free site). You can also embed ZOHO spreadsheets.
      Check out my site formulafacil.blogspot.com. There are some MS spreadsheets embedded there.
      
      Cheers,
      Reply
kannan on August 23, 2012 at 3:55 pm

The excel chart and picture displaces when the macro convert a range into a pdf file. How can I fix this issue so that the macro picks up respective cells and pics to convert into a pdf report.
Reply
Kannan on August 23, 2012 at 4:19 pm

The excel chart and picture displaces itself out of range when the vba macro converts it into an pdf report? How can this be fixed?
Reply
- Vishwamitra Mishra on August 26, 2012 at 10:46 am
  
  Hi,
  
  Can you email me the code which you used. I guess you need to resize the chart before putting it in to pdf report.
  Reply
beats on October 21, 2012 at 2:14 pm

Hi there, simply changed into alert to your blog thru Google, and found that it is really informative. I am gonna be careful for brussels. I'll appreciate in the event you proceed this in future. A lot of folks will be benefited from your writing. Cheers!
Reply
- Vishwamitra Mishra on October 21, 2012 at 2:22 pm
  
  Thanks Beats for your beautiful words !!!
  Reply
srikanthan on April 23, 2013 at 3:11 pm

Hi Mishra,

your code works great, thanks for posting. I have been trying this for many days and your post is spot on. What i was looking for, very much helpful. However, when i am able to use only SID, i am not able to connect via Service name. Can you post me an example?. Thanks a lot.

Srikanthan N.
Reply
Anil Kulkarni on June 6, 2014 at 10:00 pm

I actually found this on Google top 5 results when i was searching something! I think that says it all.
Reply
- Vishwamitra Mishra on June 6, 2014 at 10:09 pm
  
  Thanks Anil 🙂
  Reply
Anil Kulkarni on June 6, 2014 at 10:08 pm

Can you add an example to this to show how we can execute the query and copy to the result set to excel?
Reply
- Vishwamitra Mishra on June 6, 2014 at 10:10 pm
  
  Thanks for the feedback. You will get the next article with an example as well as a Free Sample Excel File with Completely configurable file.
  Reply
Enver on October 22, 2014 at 7:37 pm

Hello Vishwa,

I’m trying to run this code, but the whole excel file gets close when the macro execute the “con.Open (strCon)” line. I’m using excel 2003 in a windows xp Sp3 PC. Here is the code i made:

Private Sub Conex_Click()

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Dim Host As String
Dim Port As String
Dim SID As String
Dim User As String
Dim Password As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

‘these are not the real values
Host = Host
Port = Port
SID = SID
User = User
Password = Password

strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=” & Host & “)(PORT=” & Port & “))” & _
“(CONNECT_DATA=(SID=” & SID & “))); uid=” & User & “; pwd=” & Password & “;”

MsgBox strCon
con.Open (strCon)

End Sub

Thanks for the help!
Cheers.
Reply
manoh on November 18, 2014 at 9:59 am

Hi Vishwa,

Thanks for the knowledge sharing.The information regarding db connection is very useful in my current task.
i have a query regarding Date field format.i m fetching the db records successfully into excel but after that Date format is changed.Can u plztell me how we can keep the format of date field as it is in db while or after fetching the records using the conn string u mentioned.?

Thanks,
Manoj
Reply
Tanmay on February 1, 2015 at 1:14 pm

Can you please tell, where we can get all the information about the host, description, port number.
Reply
deepika on March 5, 2015 at 11:51 am

Hi vishwa,

Is there any macro tool available to connect to Edifecs management application
Reply
deepika on March 5, 2015 at 11:53 am

can u please help me with macro code to connect to Edifecs management
Reply
Arun Kumar on April 6, 2015 at 7:28 pm

Hi,
I try to run below script to connect Oracle DB, but its throwing error
Error message:
Run time error ‘-2147467256’ (80004005)
Automation error
Unspecified error

Sub Ora_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
‘—- Replace below highlighted names with the corresponding values
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=Arun-PC)(PORT=1521))” & _
“(CONNECT_DATA=(SID=orcl))); uid=SYS; pwd=msi13owner;”
‘— Open the above connection string.
con.Open (strCon)
‘— Now connection is open and you can use queries to execute them.
‘— It will be open till you close the connection
End Sub

Please can you tell me what’s wrong in my script.

Thanks
Arun kumar
Reply
Allwyn on May 12, 2015 at 9:21 am

HI , Thanks i am getting an Error when i use the same code, it says automation error, Unexpected error while running the last connection.str open command
Reply
- Vishwamitra Mishra on May 16, 2015 at 7:18 pm
  
  Hi Allwyn,
  
  Can you post the piece of code you are using. This error is due to incorrect syntax used in your Connection string.
  Reply
Nani on June 11, 2015 at 6:52 am

when i tried to connect oracle data base with appropriate info, got below error message. can you pls help me on this

Error:-
The Oracle(tm) client and networking components were not found.
These components are supplied by oracle corporation and are part of the oracle version 7.3(or greater)client softwre installation.

You will be unable to use this driver until these components have been installed.

What is these components, and how to install it.
Reply
- Nani on June 12, 2015 at 5:51 am
  
  Can you please help me on this
  Reply
Ricardo on June 13, 2015 at 4:14 am

Thanks for your code!!! 🙂
It helped me a lot!

Wish you success!!!
Reply
madi on June 22, 2015 at 8:52 am

Hi guys,

Great Tuto, many thanks !

How can I once connected launch a Query ?

I
Reply
Azharudeen MB on July 15, 2015 at 8:26 am

Hey,

I would like to know if it’s possible to extract data from “Oracle business intelligence discoverer desktop” to excel using a Macro. Oracle asks for a username and password before i want to enter any query.
Reply
karthik on July 15, 2015 at 4:35 pm

to connect oracle data base with appropriate info, got below error message. can you pls help me on this

Error:-
The Oracle(tm) client and networking components were not found.
These components are supplied by oracle corporation and are part of the oracle version 7.3(or greater)client softwre installation.

You will be unable to use this driver until these components have been installed.

What is these components, and how to install it.

code i’m using

Sub Ora_Connection()
Dim con
Dim rs
Dim query As String
Set con = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)

‘—- Replace below highlighted names with the corresponding values
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=dweorddbv1.hq.bn-corp.com)(PORT=1521))” & _
“(CONNECT_DATA=(SID= einvd))); uid=INV_STG; pwd=INV_STG111;”
‘— Open the above connection string.
con.Open (strCon)

‘— Now connection is open and you can use queries to execute them.
‘— It will be open till you close the connection
End Sub
Reply
- Vishwamitra Mishra on July 16, 2015 at 7:41 am
  
  Hi,
  
  You can refer the Oracle website to get the relevant Oracle client. Without installing that you would not be able to use this VBA code.
  
  Oracle Client 11g: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
  Reply
  - Hi Viswamitra on November 18, 2015 at 10:12 am
    
    What about for windows 8.1 (64bit)..which one i have to download…for similat type of issue
    Reply
Vinay on August 19, 2015 at 5:41 am

Am trying to connect oralce Database through Excel Macro..
But when i run script i got below error message.
Can you please suggest me how to resolve this issue
Error:-
[Microsoft][ODBC driver for oracle][Oracle]Error while trying to retrive text for error ORA-01019
Reply
Jignesh Agrawal on July 2, 2016 at 10:26 am

After service name code, I am getting error “[Microsoft][ODBC driver for Oracle][Oracle]ORA-28030: Server enocountered problems accessing LDAP directory service”

Please help.
Reply
- Jignesh Agrawal on July 2, 2016 at 10:28 am
  
  Moreover I wanted to connect oracle server on cloud.
  Reply
keshav kant singh on August 12, 2016 at 5:56 am

Hello guys

can you please help me in connecting postgresql data base through excel vba.

thanks in advance
Reply
Ashok on September 25, 2016 at 6:17 pm

strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=Your Host Name)(PORT=Enter Port Number))” & _
“(CONNECT_DATA=(SERVICE_NAME=database))); uid=Enter User ID; pwd=Enter Password;”

Above code is not working for me, i connected using server name…
plz guide me on above syntax!
Reply
jaiwanth on November 11, 2016 at 4:50 am

superb piece of code worked like a charm, many thanks for this wonderful site and efforts taken to help many like me.
Reply
Gaurav on December 1, 2016 at 7:46 am

Hi Vishwa, i am getting below error

Run-time error ‘-2147467259 (80004005)’:
LMicrosoftlLODEC Driver Manager) Data source name not found and no
default driver specified

strcon = “Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=XXXX))(CONNECT_DATA=(SID=XXXX))); uid=XXXX; pwd=XXX;”

‘Open the ODBC Connection using this statement
cnDB.Open strcon
Reply
- Vishwamitra Mishra on December 1, 2016 at 9:12 am
  
  Hi Gaurav,
  
  You need to install ODBC driver in your system in order to make a connection to database. You can refer the link for drivers to download and install. http://www.oracle.com/technetwork/database/windows/index-098976.html
  Reply
gita on December 6, 2016 at 6:31 am

hi
i am using the code provided by you..but i want to know the parameters how to give?from where i will get? i created odbc connection for oracle db .pls help me
Reply
Roshan on December 30, 2016 at 7:20 pm

Hi,

I need your help as i am getting error as below:

Run-time error ‘-2147467259 (80004005)’:
Automation Error
Unspecified Error

Below is the code I am using.

Sub Ora_Connection()

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=xyzhostname)(PORT=1521))” & _
“(CONNECT_DATA=(SERVICE_NAME=xyzservicename))); uid=username; pwd=dummypwd;”

con.Open (strCon)

End Sub

Please help its urgent.
Reply
- Vishwamitra Mishra on December 30, 2016 at 10:08 pm
  
  Hi, can you run the program in debug mode and let me know at which step you are getting this error?
  Reply
  - Roshan on December 31, 2016 at 2:13 pm
    
    I am getting error on this line
    con.Open (strCon)
    Reply
  - Roshan on January 1, 2017 at 5:31 am
    
    I am getting error on this line.
    
    con.Open (strCon)
    
    Please help. I tried all combination but still same.
    Reply
    - Vishwamitra Mishra on January 2, 2017 at 8:57 pm
      
      Hi,
      
      Have you installed oracle client in your system. Easy way to check is try connecting your Databse by TOAD or any other client. If you are unable to, then you can install the oracle client from the Oracle website. This should solve the problem.
      Reply
      - Roshan on January 8, 2017 at 9:00 am
        
        I installed the oracle client from here http://www.oracle.com/technetwork/topics/winx64soft-089540.html
        and set the path accordingly
        
        Also I have Oracle SQL Developer installed, i am able to query from here.
      - Vishwamitra Mishra on January 8, 2017 at 8:36 pm
        
        Ok then there must be a problem with the connection string you are using. Kindly check the syntax and input as per the string format I have shared here on this page.
  - sonali on May 2, 2018 at 12:36 pm
    
    hi vishwa, my same code is not working it is always asking me for the data source name…. can u help me how to find that.?
    Reply
    - Vishwamitra Mishra on May 29, 2018 at 3:11 pm
      
      Hi Sonali,
      
      Without looking at your code, I can not help you with this. With the error you mentioned.. it looks like there some issue with the Source Name/path etc. Can you share your connection string by masking the real data.
      Reply
Shaik on June 23, 2018 at 11:04 am

hi I am getting below error:

[Microsodt] [ODBC driver for oracle] [Oracle] ORA-1215 ;TNS could not resolve the connect identifier specified

strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=srvXXXXXo.XXXXXXXk.us-east-1.rds.amazonaws.com)(PORT=1648))” & _
“(CONNECT_DATA=(SERVICE_NAME=DBTEST))); uid=db_username; pwd=XXXXXX;”
‘— Open the above connection string.
con.Open (strCon)
Reply
Saif on June 11, 2019 at 4:53 pm

Hi Friend,

can you please help the connect the oracle using vba in Windows10, as above code is only working Windows7.

Thank You,
Saif
Reply
Moses on October 31, 2020 at 8:08 pm

Sub Ora_Connection()

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
‘— Replace below highlighted names with the corresponding values
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=SCOTTUSER” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=localhost)(PORT=1521))” & _
“(CONNECT_DATA=(SERVICE_NAME=orcl))); uid=scott; pwd=tiger;”
‘— Open the above connection string.
con.Open (strCon)
‘— Now connection is open and you can use queries to execute them.
‘— It will be open till you close the connection
query = “select * from emp;”

End Sub

i am getting a run time error …..Please Help!!!!!
Reply
Saranya on August 23, 2022 at 11:22 am
Hi,
I’m getting Data source name not found and no default driver specified run time error for the below code. Please help me to resolve this issue
```
strconnection= "Driver={Microsoft ODBC for Oracle}; " & 
"CONNECTSTRING=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) 
(HOST=localhost)(PORT=1521))" & 
"(CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME=orcl))); uid= userid; pwd=password;"
```
Reply
- Vishwamitra Mishra on August 24, 2022 at 7:49 am
  Hi Saranya, try the following code and it should work:
  
  strconnection= "Driver={Microsoft ODBC for Oracle}; " & "CONNECTSTRING=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT=1521))" & "(CONNECT_DATA=(SERVICE_NAME=orcl))); uid= userid; pwd=password;"
  
  Reply