Excel VBA to SQL Server ADODB Connection - Prompt for User ID and Password

Question

Excel VBA

The following ADO DB Connection string works and fetches data from the database into Excel

Const rspADO As String = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"Initial Catalog=MyDatabase;" & _
"Data Source=118.aaa.yy.xx;" & _
"User ID=Username;Password=password;"

How do I prompt the user for input of Username and Password (at runtime) using the Data connection prompt (and not the Inputbox or a Userform in Excel)?

Thanks in advance for the help.

Solution

If you use the ado db.connection you can according to this vba express post, code extract:

Dim dbConnectStr As String
Set con = New ADODB.Connection

dbConnectStr = "Provider=msdaora;Data Source=" & "Oracle_Database_Name;"

con.ConnectionString = dbConnectStr
con.Properties("Prompt") = adPromptAlways
con.Open dbConnectStr 'ConnectionString

I've also found you may need to set the Prompt property to adPromptComplete.