Search code examples
vbaoracleoraoledb

How to configure OraOLEDB connection string

I need some help to set up a connection to Oracle DB.

I have an Excel VBA application using following working connection string setting up proper connection to existing Oracle database:

Provider = OraOLEDB.Oracle; 
Data Source = XXXXSSA5; 
User Id = [USERNAME]; Password = [PASSWORD];

Recently the database was moved to the new localisation, the following connection string is working fine with sqlplus, however I have no idea how to properly represent it as OraOLEDB connection string:

[USERNAME]/[PASSWORD]@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=symbol.server.company.net)(Port=1731))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DDDB1044_APP)))

I tried with filling in recent template with a new data:

Provider = OraOLEDB.Oracle; 
Data Source = DDDB1044_APP; 
User Id = [USERNAME]; Password = [PASSWORD];

However I suppose that I'll need to somehow pass Host name to the connection string and probably the Port too, as I am receiving the message: ORA-12154: TNS:could not resolve the connect identifier specified

Solution

  • Usually you define an alias in your tnsnames.ora file like this:

    DDDB1044_APP = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=symbol.server.company.net)(Port=1731))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DDDB1044_APP)))

    Then use this alias Data Source = DDDB1044_APP;

    If you don't like to work with alias then just put the entire connection to it:

    Data Source = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=symbol.server.company.net)(Port=1731))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DDDB1044_APP)))"