Some Utility Codes

Some Utility Codes

While working with Oracle DB's,  you require TNS entry file (tnsnames.ora) in your system whenever you fire a query to a particular DB.
So when you fire query using ADODB objects to oracle DB's , you may require to have the TNS entry in that file else you would find error stating "TNS entry missing".

I struggled this kind of scenario, to have someone from Admin team to replace file, due to lack of administrative privileges. Some organizations don't allow that. I thought to eliminate this, so Googled for TNS entrly less Connection String.

I saw that a TNS file have below type of information

................................................................

SomeRefrenceName=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = theDBHostServer.Companyname.com) (PORT = 1530))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SomeServiceName)
    )
  )

................................................................

So I thought can we enter this TNS Entry into our connection string? I found below solution.

Below provider is from MicroSoft for Oracle DB, This seemed to be reliable for me.

ConStr =  "provider=msdaora;" _ 

          & "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" _ 

          & "HOST=" & strHOST & ")" _

          & "(PORT="& strPORT & "))" _

          & "(CONNECT_DATA=(SERVICE_NAME="& strSERVICE & ")))" _

          & ";User Id=" & strUID & ";Password=" strPWD & ";"

You can use other providers as well like

Provider=MSDAORA.1 

provider=OraOLEDB.Oracle.1

-Pankaj Dhapola 

Let's Think on it