VBA: Connect to Oracle DB account without a Password

[dhancy]

I have a VBA script that have been using for several years to connect to our Oracle database. It looks like this:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=OraOLEDB.Oracle; Password=myPW; User ID=myUser; Data Source=myDB", _
         Destination:=Range("$A$1")).QueryTable

It has always worked flawlessly until our agency made a change. We are now using a single sign-in approach for Oracle. The user name is EXTERNAL and the password is blank. Authentication is now performed externally based on our windows sign in.

What syntax changes are needed in the above code when the password is blank/null?

Thank you!

Dennis

 

[John_w]

Try:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=OraOLEDB.Oracle;Data Source=myDB;OSAuthent=1;", _
         Destination:=Range("$A$1")).QueryTable

Source: Trusted Connection - OraOLEDB.Oracle - ConnectionStrings.com

 

[dhancy]

Thanks John,

I tried that, but it's still not connecting. Instead, I see this window pop-up.

 

[John_w]

I don't have Oracle to test this with.

I can only suggest recording a macro with the steps on this page and see what connection string it generates:

OLE DB Power Query Data Connector - Power Query

Provides basic information and connection instructions for the OLE DB connector.

learn.microsoft.com