Oracle – ODBC connection using MS Access error (ORA-12154)

databasems-accessodbcora-12154oracle

I am trying to use MS access to connect to a Oracle database.
I keep on getting the following error message:

ORA-12154: TSN- could not resolve the connect identifier secified

The Oracle Drivers OracleClient10g can verify that the database server exists.

I have a section in my tsnnames.ora file that looks like this:
UBASEP10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bxxx-xxx.yyyy.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = UBASE)
)
)

per my attempts to get this error resolves I added this to the sqlnet.ora file:

NAMES.DIRECTORY_PATH= (HOSTNAME, ONAMES, TNSNAMES,LDAP,EZCONNECT)

When using the Windows ODBC driver configuration utility it asks for the following
following information
DATA SOURCE NAME : MYSOURCE NAME
TSN SERVICE NAME:UBASEP10G
USERID:MYUSERID

any suggestions ?????

Best Answer

I don't have Access, but using Excel 2007, I had to do the following:

  1. Open ODBC Administrator (in the Administrator Control Panel)
  2. For either User DSN or System DSN, click Add...
  3. Select "Oracle in OraDb10g_home1" as the driver
  4. In the Oracle ODBC Driver Configuration, I entered:
    • Data Source Name: myOracleDsn
    • Description: This is my DSN for my Oracle Database
    • TNS Service Name: oratns
    • User ID: scott
  5. Click Test Connection, and enter "tiger" for the password. Obviously, I'm connecting to my scott/tiger sample Oracle database. Also, when I installed Oracle, I picked oratns as my TNS name. The other two values above are arbitrary -- you'll see them in dialogs in Excel.
  6. If the test works, click OK.
  7. Go to Excel and make a new connection to a DSN. The dialog will show "myOracleDsn" as an option. Select it, enter "tiger" for the password again, and you should get a working connection.
Related Topic