MSOLEDBSQL Connection String for SQL Server

[turagittech]

I am working to create a connection from Excel Desktop Office 365 subscription in a macro.

I have the following code in the VBA

PType = "MSOLEDBSQL"
Hostname = "tcp:myserver.database.windows.net""
InitCatalog = "TEST"
UserName = "xx@myserver"
Passwd = "bigpassword01"
Trusted = "yes"

sConnectionString = "Provider = " & PType & "; " _
& "Server = " & Hostname & "; " _
& "Initial Catalog = " & InitCatalog & "; " _
& "User ID = " & UserName & "; " _
& "Password = " & Passwd & "; " _
& "Trusted_Connection = " & Trusted & "; "
'& Encrypt = " & Enc "

When I hit the connection.open part of my code I get an Invalid connection string attribute error

This is what it looks like from the Watch variable 

"Provider = MSOLEDBSQL; Server = tcp:myserver.database.windows.net; Initial Catalog = TEST; User ID = xx@myserver; Password = bigpassword01; Trusted_Connection = yes; "

There aren't many examples of people using the MSOLEDB connection string

What bit's missing or is an invalid attribute VBA is terrible about helping out?

Peter

[#2]

@turagittech 

I almost always use Power Query in Excel now so it has been a while since I have done this but I don't think you can provide a username and password and also have Trusted_Connection = Yes

[#3]

@John Twohig I found the answer and yes you can do that as well as their is a constuct you need, it turned out a character in the password was throwing us off. After a bit of testing we found suitable characters.

This format will work for VBA withe the MSOLEDBSQL driver/provider 

 sConnectionString = "Provider = " & PType & "; " _
                        & "Data Source = " & Hostname & "; " _
                        & "Initial Catalog = " & InitCatalog & "; " _
                       & "User ID = " & UserName & "; " _
                        & "Password = " & Passwd & "; " _
                        & "Trusted_Connection = " & Trusted

I will leave others to fill in the rest, but a reminder to check out the strong passwords rules and the disallowed characters for connection strings. I did a blog post with all the details if someone needs additional information http://blog.pameacs.com/2019/07/connection-strings-for-vba-and-msoledb.html