Cannot connect to Oracle 11g via MS Access using ODBC

[harringtonc]

Hi,
I am having an issue where I cannot connect to Oracle 11g using MS Access.
This used to work fine until the Oracle DB was upgraded from 9i to 11g. Now
the ODBC connection request will always fail.

I have already tested the 11.1.0.7 Oracle ODBC connection in the ODBC Data
Sources Administrator and the connection is fine.

At first I though that the configuration of the Oracle listener and TNS
entries were wrong but this is not the case. I also thought that the
connection code in the Access MDB VBA code needed to change, so I decided to
do a very simple test.

I opened MS Access 2000 (also 2007) and selected Get External Data / Import
table. I selected my 11g ODBC connection and I get a "TNS: No listener"
error. I do not have this problem trying to connect to Oracle 10g. I have
already verified that there are no problems with the Oracle listener and I
can connect using other clients.

Does anybody know of an inability of MS Access to connect to an Oracle 11g
database?

Thanks,
Carl

[Douglas J. Steele]

I thought ODBC was ODBC! Can you connect to the database from any other ODBC
client? (say, Excel?)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"harringtonc" <harri...@discussions.microsoft.com> wrote in message
news:B5AA00EF-BEB8-4773...@microsoft.com...

[harringtonc]

Hi Doug,
Thanks for the reply.
I tried to connect to the 11g database using the 11g ODBC data source in
Microsoft Query (from Excel) and I get the same an ORA-12505 listener does
not know of the SID. This is a different error than I get on the database
server.

I am running the Excel attempt from my own PC (don't have Microsoft query
installed on the server).

I get the ORA-12505 from both Excel and Access on my PC but ORA-12541 from
Access on the DB server. Perhaps it's a config issue but the TNS entries I'm
using work fine for non ODBC connections.

Thanks,
Carl

I was getting an ORA-12541 no listener error when connecting from MS Access

"Douglas J. Steele" wrote:

> .
>

[harringtonc]

Got it sorted!
I changed the port number of the 11g listener back to 1521.
It was 1522. Don't know what was using 1522 or why connections to the 11g DB
worked from TOAD, app server, etc using the 1522 listener.
But it all works using 1521 listener so I'm happy ;^)