0

I am trying to create an Oracle connection string in Excel VBA. After lots of research, I have a working connection string:

Set Oracle_conn = New ADODB.Connection
Oracle_conn.Open "ODBC; Driver='Oracle in instantclient_19_9'; Data Source='Data Mart'; UserID=" & ID & "; Password=" & Password & ";"

However, this only works when my ODBC Data Source is a User DSN. When the ODBC Data Source is a System DSN, I end up with: "[Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied"

The reason I am using System DSN is because my code runs from a shared PC, so System DSN works for all users without each individual user needing to create a User DSN.

Any advice on how to tweak my code such that it will run with a System DSN would be greatly appreciated. Thank you.

Edit 1: System DSN connects successfully if I manually go to Data ribbon > Get Data > From Other Sources > From ODBC

Edit 2: I still haven't been able to figure out how to connect to a System DSN with VBA code. Maybe it's a bug with the ODBC driver? I realize this is a bit of an obscure question, but I'm still hopeful someone can help me connect. Thank you!

Improve this question

1 Answer 1

Reset to default
0

Thanks for the question, I am not sure if this will work for Oracle, however for MSSQL I was having issues connecting to a System DSN. Basically I was over specifying elements that were already configured with ODBC Data Sources.

To get my System DSN all I needed was conn.ConnectionString = "Data Source=" & <DSN_NAME (string)> & ";"

Credit to the original poston Experts Exchange

To assist with administration and allowing it to be remotely pushed, I store the DSN name as a System Environment variable. In VBA I set a local variable to the DSN name with DSN_NAME = Environ("ENV_DSN_NAME")

Improve this answer
1
  • Thank you lifedroid for your response. I had all but given up hope. I set up the consolidated string as you described, and created the System Environment variable as well. Unfortunately, I ended up with the same ORA-01017 error as described in the original post. Very strange. I even tried ChatGPT to try to generate some code, and it came up with code pretty similar to my original code above. It led to the same ORA-01017 error. My username and password are definitely correct, and I am able to connect successfully under the ODBC Test Connection window. Commented Aug 28, 2023 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.