0

At work I programmed a little something in access vba to automatically create mails and send them out. To get all the information needed, I need to get access to a server of my company. Everything worked for me: Programmed the vba, installed oracle instant client 12_1 and the odbc part both in C:\Oracle\instantclient_12_1 (both 32bit), installed the odbc_install.exe and created the tnsnames.ora-file in the same directory.

When I try to do the same for my colleagues, eventually the: Run-time Error '-2147467259 (80004005) [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified appears.

I have absolutely no idea why. Tried to install 64bit driver, which can clearly not work :P Tried to change the connection strings in vba and the tnsnames.ora file...

I did not yet create a user-dsn connection, which I really want to avoid doing and was not really necessary.

Do you have an idea what could be the problem or how I can fix this?

.

.

Here are the connection strings I used in vba:

Set CMDB_cn = New ADODB.Connection Set CMDB_rst = New ADODB.Recordset Dim strConnect, str1 As String strConnect = "Driver={Oracle in instantclient_12_1};Dbq=blabla;Uid=user;Pwd=swordfish;" str1 = "select * From db.table WHERE Name = 'Mr. Nobody';" CMDB_cn.Open strConnect

And tnsnames.ora:

blabla=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=123.456.789.123)(PORT=1234)))(CONNECT_DATA=(SID=serverName)(SERVER=DEDICATED)))
Marke
  • 1
  • 2
  • Not a solution for your problem but.... Are you aware that you can send e-mails directly from Oracle (both plain text as well as full HTML documents)? – FDavidov Nov 30 '16 at 08:28

2 Answers2

1

What do you mean by "created the tnsnames.ora-file in the same directory"

Location of tnsnames.ora file can be determined by several ways:

  • Directory where your running .exe file resides. (Most likely not a smart idea for MS-Access program folder)
  • Your current working directory
  • Folder as specified by Environment Variable TNS_ADMIN
  • Folder as specified in Registry value HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient12_1_home1\TNS_ADMIN (or similar)
  • Folder %ORACLE_HOME%\network\admin, i.e. C:\Oracle\instantclient_12_1\network\admin

I don't know whether all of these conditions apply for ODBC, also I don't know the order of precedence, I never found any documentation about that.

You should check all of them, usually Environment Variable TNS_ADMIN has highest precedence.

Actually I don't know whether Connection-String is case-sensitive. Try DBQ instead of Dbq

btw, did you remove the 64-bit Oracle Client again? If not then your PATH variable may still point to 64-bit client which will not work with 32-bit Access and 32-bit ODBC driver.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

Thanks, Wernfried! You got me to the right direction!

I didn't create the environment variables at the other computers:

Oracle_Home -> Path to the folder e.g. C:\Oracle\instantclient_12_1 TNS_ADMIN -> Path to the tnsnames.ora file e.g. C:\Oracle\instantclient_12_1\network (or wherever you saved it!)

now restart.

The connection in the code (for me vba) will now find the tnsnames file and be able to resolve the connection string to the databank.

Marke
  • 1
  • 2