Excel VBA to SQL Server ADODB Connection - Prompt for User ID and Password
excel, sql-server, vba
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`.
Problem
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.