Oracle Connection String in VBA

.

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

Adsense

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.

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

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
How to Declare a Public Variable in VBA

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

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 (=).

You May Also Like…

54 Comments

  1. Kannan

    is there any free vba obfuscator to protect my modules and vba project?

    Reply
    • Vishwamitra Mishra

      Hi Kannan,
      Sorry i do not know any tool as such.
      Thanks,
      Vish

      Reply
      • Kannan

        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

          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
  2. kannan

    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
  3. Kannan

    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

      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
  4. beats

    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

      Thanks Beats for your beautiful words !!!

      Reply
  5. srikanthan

    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
  6. Anil Kulkarni

    I actually found this on Google top 5 results when i was searching something! I think that says it all.

    Reply
    • Vishwamitra Mishra

      Thanks Anil 🙂

      Reply
  7. Anil Kulkarni

    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

      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
  8. Enver

    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
  9. manoh

    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
  10. Tanmay

    Can you please tell, where we can get all the information about the host, description, port number.

    Reply
  11. deepika

    Hi vishwa,

    Is there any macro tool available to connect to Edifecs management application

    Reply
  12. deepika

    can u please help me with macro code to connect to Edifecs management

    Reply
  13. Arun Kumar

    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
  14. Allwyn

    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

      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
  15. Nani

    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

      Can you please help me on this

      Reply
  16. Ricardo

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

    Wish you success!!!

    Reply
  17. madi

    Hi guys,

    Great Tuto, many thanks !

    How can I once connected launch a Query ?

    I

    Reply
  18. Azharudeen MB

    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
  19. karthik

    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
  20. Vinay

    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
  21. Jignesh Agrawal

    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

      Moreover I wanted to connect oracle server on cloud.

      Reply
  22. keshav kant singh

    Hello guys

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

    thanks in advance

    Reply
  23. Ashok

    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
  24. jaiwanth

    superb piece of code worked like a charm, many thanks for this wonderful site and efforts taken to help many like me.

    Reply
  25. Gaurav

    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
  26. gita

    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
  27. Roshan

    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

      Hi, can you run the program in debug mode and let me know at which step you are getting this error?

      Reply
      • Roshan

        I am getting error on this line
        con.Open (strCon)

        Reply
      • Roshan

        I am getting error on this line.

        con.Open (strCon)

        Please help. I tried all combination but still same.

        Reply
        • Vishwamitra Mishra

          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
          • Vishwamitra Mishra

            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

        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

          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
  28. Shaik

    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
  29. Saif

    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
  30. Moses

    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
  31. Saranya

    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

      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				

Trackbacks/Pingbacks

  1. Learn Excel Macro Query Database and Spreading the Records in Excel - VBA Code - [...] = window.adsbygoogle || []).push({}); In continuation to one of my article which shares the connection strings to connect to…
  2. How to connect to Access Database - ADO Connection String - Let's excel in Excel - […] How to Connect to Oracle Database using Excel Macros […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest