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!

Andy
  • 1
  • 1
  • 2

1 Answers1

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")

lifedroid
  • 1