How can I connect to an Oracle database from excel VBA using LDAP?

Question

We have several excel spreadsheets connecting to an oracle database using an ADODB connection (see example code below). The connection is made using TNSNAMES which is setup on the client machine using the oracle instaclient driver.

Set oCN = CreateObject("ADODB.Connection")
oCN.CursorLocation = 3
oCN.CommandTimeout = 0

cCN11 = "DRIVER=Oracle in instantclient11_1;"
strDatabase = "DBQ=DATABASE_NAME;"
strID = "UID=YourID;"
strPassword = "PWD=YourPassword;"

oCN.Open cCN11 & strDatabase & strID & strPassword

This code works fine. However, a decision has been made to move away from maintaining TNSNAMES.ORA to use LDAP instead. I've tried to find some information on how to use LDAP to connect via VBA but I have been unable to find any useful information.

I am not sure if the ADODB connection allows LDAP connection strings or if I need to use something else entirely. If anyone can point me in the right direction it would be appreciated.

EDIT

Just to give more background on my setup. The 32 oracle client is installed on our network drive (z:\oracle11). The client machines using this have the driver information in the registry here:

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\Oracle in instantclient11_1

The "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ORACLE" has the ORACLE_HOME and TNS_ADMIN set to point to the oracle client installed (z:\oracle11). The TNSNAMES.ORA sits in root of the install folder (z:\oracle11).

This setup has been in place years and works fine when connecting via TNSNAMES.

The connection strings site says that the format for the connection string should be as follows if you want to do a TNSLESS connection

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));

uid=myUsername;pwd=myPassword;

Based on the comments and answers and some further research setting the connection string using the code below should work. I have to include the driver in the connection string

I have built the connection string using the current TNSNAMES.ORA data (The HOST, PORT, SERVICE_NAME, UID and PWD have been redacted for security reasons)

Dim sSQL As String
Dim oRS As ADODB.Recordset  'ADODB.Recordset
Dim oCN As ADODB.Connection

Set oCN = CreateObject("ADODB.Connection")
oCN.CursorLocation = 3
oCN.CommandTimeout = 0


' Build the connection string
strConnectionString = "DRIVER=Oracle in instantclient11_1;DBQ=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=my_ host)(PORT=1234)))(CONNECT_DATA=(SERVICE_NAME=myhost.world)(SERVER=DEDICATED)));Pwd=my_password;Uid=my_user_id"

' Open the connection using the connection string
oCN.Open strConnectionString

Unfortunately I get the error message I would get if the "DBQ" didn't exist in the TNSNAMES file.

[Oracle][ODBC][Ora][ORA-12154: TNS:could not resolve the connect identifier specified.

If I change the DBQ to SERVER (as in the connection string example) I get a different error message:

[Oracle][ODBC][Ora][ORA-12560: TNS:protocol adapter error.

I've done a NSLOOKUP against the "my_host" value and it gets resolved so I know that is correct. The port number is correct. The service name is also correct , so is the user name and password.

I've also tried using "Microsoft ActiveX Data Objects 2.8" library and the 6.1 version neither of which make any difference.

ANSWER

To clarify Wernfried Domscheit answer you do simply change the "DBQ=DATABASE_NAME" so the DATABASE_NAME value is your DNS value. Many thanks Wernfried.

I was clearly trying to overthink this. I am sure I must have tried that first but obviously not. Good job I tried it again, and read the answer...again.

Answer 1

DATABASE_NAME is just an alias for your database. It does not make any difference wether such alias is resolved by tnsnames.ora file or by an LDAP server.

So, there is no difference. Just set your TNS_ADMIN variable as you do for an tnsnames.ora file.