question

JoeSweeney-0019 avatar image
0 Votes"
JoeSweeney-0019 asked JoeSweeney-0019 answered

MS Access to Oracle SQL Developer via ODBC

I had posted this in the Oracle Community. They closed my post and told me to check with the .Net/Windows community.

122818-oracle-odbc-1.png122819-oracle-odbc-2.png
I am developing an app for a client that will be using Oracle for the back end and MS Access for the front end.

The attached image is the error I get when attempting to test the connection in the ODBC Data Sources (64-bit) Administrator.

For username/password, I've tried: case as created, all lower case, all upper case, and other combinations.

Windows 10 Pro, Version 21H1, Build 19043.1110

Oracle SQL Developer Version 20.2.0.175, Build 175.1842

MS Access 2019 Version 2107, Build 14228.20226 (standalone - not part of Office 365)

TNSNAMES.ORA file. This is the entry:

PTU =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ASUS_G703GX)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PTU)

)

)

Any suggestions would be greatly appreciated.

Image

Image


sql-server-general
oracle-odbc-1.png (18.3 KiB)
oracle-odbc-2.png (25.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoeSweeney-0019 avatar image
0 Votes"
JoeSweeney-0019 answered

I found a solution. I downloaded the trial version of Altova DatabaseSpy. It created the ODBC Data Source and connected without any trouble.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

The error ORA-01017 is raised by the Oracle database engine, not by MS Access nor ODBC Admin; see http://www.dba-oracle.com/t_ora_01017.htm

case as created, all lower case, all upper case, and other combinations.

Passwords are case sensitive, don't make any sense to try case combinations, you have to use the right case for password.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered JoeSweeney-0019 commented

Hi @JoeSweeney-0019,

invalid username/password; logon denied

Quote from the blog How to Resolve ORA-01017 Invalid Username/Password; Logon Denied.

There are a few reasons that you can get this error:

• Your username or password is actually incorrect
• The database configuration is not correct (tnanames.ora, $ORACLE_SID parameter)

There are a few ways to resolve the ORA-01017 error:

• Check the username and password are correct
• Check the database link setup if you’re using a database link


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you but I'd previously found this article and neither of the suggestions are the issue. I copy/paste my username and password from a text editor so those are correct. The TSNAMES.ORA file has the entry.

The issue is that the ODBC Data Source I'm creating cannot locate the user.

0 Votes 0 ·

I copy/paste my username and password from a text editor so those are correct.

Silly question: and you have used them in the same manner to log into to Oracle directly?

What if you instead of using a DSN, put them in a connection string?

Note that the forum you are posting is for SQL Server, and you are using Access + Oracle. That is, while we try to help, your question is a bit outside of our expertise.

0 Votes 0 ·

Not a silly question. Yes, I use them in the same manner to log into Oracle directly.

I did not realize I was in the SQL Server forum. I was attempting to post it either generally or as an MS Access.

0 Votes 0 ·