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:

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!