{"id":12082,"date":"2011-11-11T18:20:22","date_gmt":"2011-11-11T18:20:22","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=417"},"modified":"2022-08-06T12:12:14","modified_gmt":"2022-08-06T12:12:14","slug":"oracle-connection-string-in-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/11\/oracle-connection-string-in-vba\/","title":{"rendered":"Oracle Connection String in VBA"},"content":{"rendered":"

Dear Friends,<\/p>\n

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.
\nWe can do connection with Oracle either by giving SID <\/strong>(Oracle S<\/strong>ystem ID<\/strong>) or Service Name<\/strong>. Whichever is available for the connection, we can use them to connect to the Oracle Database.<\/p>\n

Prerequisite<\/h2>\n

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.<\/p>\n

Step to Add ADODB Connection References in Excel <\/h2>\n
\n1.<\/strong> Go to VB Editor Screen (Alt+F11)
\n2.<\/strong> Tools \u2013> References\u2026
\n3.<\/strong> From the List of Available References Select \u201cMicrosoft ActiveX Data Objects 2.0 Library<\/strong>\u201d You can select 2.0 Version or any other higher version of this reference.
\n4.<\/strong> Click OK\n<\/div>\n

\u00a0 Connection String with SID\u00a0<\/a>
\nConnection String with Service(DB Name)\u00a0<\/a><\/p>\n

<\/a><\/p>\n

Oracle Connection String with SID:<\/strong><\/h2>\n

Use the below Code for connecting to the Oracle Database.<\/p>\n

<\/a><\/p>\n

\r\n\r\nSub Ora_Connection()\r\nDim con As ADODB.Connection\r\nDim rs As ADODB.Recordset\r\nDim query As String\r\nSet con = New ADODB.Connection\r\nSet rs = New ADODB.Recordset\r\n'---- Replace below highlighted names with the corresponding values\r\nstrCon = \"Driver={Microsoft ODBC for Oracle}; \" & _\r\n\"CONNECTSTRING=(DESCRIPTION=\" & _\r\n\"(ADDRESS=(PROTOCOL=TCP)\" & _\r\n\"(HOST=Your Host Name)(PORT=Port Number))\" & _\r\n\"(CONNECT_DATA=(SID=SID of your Database))); uid=User ID; pwd=Password;\"\r\n'---  Open \u00a0\u00a0the above connection string.\r\ncon.Open (strCon)\r\n'---  Now connection is open and you can use queries to execute them. \r\n'---  It will be open till you close the connection\r\nEnd Sub\r\n<\/code>\r\n<\/code><\/pre>\n
<\/a><\/p>\n
Oracle Connection String with Service:<\/strong><\/h2>\n
Use the below Code for connecting to the Oracle Database.<\/p>\n
<\/a><\/p>\n
\r\n\r\n\r\nSub Ora_Connection()\r\nDim con As ADODB.Connection\r\nDim rs As ADODB.Recordset\r\nDim query As String\r\nSet con = New ADODB.Connection\r\nSet rs = New ADODB.Recordset\r\n'---  Replace below highlighted names with the corresponding values\r\nstrCon = \"Driver={Microsoft ODBC for Oracle}; \" & _\r\n\"CONNECTSTRING=(DESCRIPTION=\" & _\r\n\"(ADDRESS=(PROTOCOL=TCP)\" & _\r\n\"(HOST=Your Host Name)(PORT=Enter Port Number))\" & _\r\n\"(CONNECT_DATA=(SERVICE_NAME=database))); uid=Enter User ID; pwd=Enter Password;\"\r\n'---  Open the above connection string.\r\ncon.Open (strCon)\r\n'---  Now connection is open and you can use queries to execute them.\r\n'---  It will be open till you close the connection\r\nEnd Sub\r\n<\/code>\r\n<\/code><\/pre>\n
Read this Also:<\/h2>\n