how to specify database user credentials in a connection array string (Excel VBA)

Please help in modifying the connection array string (Excel 2010) to be able to receive the input from a cell reference for fields UID=;PWD= and Initial Catalog.

.Connection= Array("OLEDB;Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=NZSQL;Database=consumerdb;Servername=192.54.97.102;", "UID=USERNAME;PWD=****;Port=5480;ReadOnly=0;SQLBitOneZero=0;LegacySQLTables=0;NumericAsChar=0;ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateFormat=1;SecurityLevel=preferredUnSecured;CaCertFile="";Initial Catalog=CONSUMERDB_USERNAME")

Solution

The Connection string is an array of simple strings. You need to locate the one containing the user name and password, then break that section apart and insert the values from the cell. Put sections of the connection string on different lines to help break it up. Break up each section if it helps to isolate the substring that you want to manipulate.

.Connection = Array("OLEDB;Provider=MSDASQL.1;Persist Security Info=True;" & _
                    "Extended Properties=""DSN=NZSQL;Database=consumerdb;Servername=192.54.97.102;", _
                    "UID=USERNAME;PWD=****;" & _
                    "Port=5480;ReadOnly=0;SQLBitOneZero=0;LegacySQLTables=0;NumericAsChar=0;" & _
                    "ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateFormat=1;" & _
                    "SecurityLevel=preferredUnSecured;CaCertFile="";Initial Catalog=CONSUMERDB_USERNAME" _
                   )

Now if is easy to find the username and password and concatenate the values into that portion.

Dim usr As String, pwd As String
usr = Range("A1").Value
pwd = Range("B1").Value
.Connection = Array("OLEDB;Provider=MSDASQL.1;Persist Security Info=True;" & _
                    "Extended Properties=""DSN=NZSQL;Database=consumerdb;Servername=192.54.97.102;", _
                    "UID=" & usr & ";PWD=" & pwd & ";" & _
                    "Port=5480;ReadOnly=0;SQLBitOneZero=0;LegacySQLTables=0;NumericAsChar=0;" & _
                    "ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateFormat=1;" & _
                    "SecurityLevel=preferredUnSecured;CaCertFile="";Initial Catalog=CONSUMERDB_USERNAME" _
                   )