RefreshAll VBA: How to pass credentials?

[scalaria]

Hello everyone. First post around here.
Thanks to several threads I learned how to establish a connection to a DB to update data sources that feed tables and then pivot tables in an Excel file.

Public Sub test()

Dim conn As New ADODB.Connection
Dim server_name As String
Dim database_name As String
Dim user_id As String
Dim password As String

'----------------------------------------------------------------------
' Establish connection to the database
server_name = "myserver" 
database_name = "mydatabase" 
user_id = "myUserID" 
password = "******" 

Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 5.1 Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=16427" ' Option 16427 = Convert LongLong to Int: This just helps makes sure that large numeric results get properly interpreted

ActiveWorkbook.RefreshAll

'-----------------------------------------------------------------------
' Close connections
On Error Resume Next
'rs.Close
'Set rs = Nothing
conn.Close
Set conn = Nothing
On Error GoTo 0
End Sub

The problem I have is that this prompts me again for UserID and Password. My goal is to fully automate the process. I don't to want to hard code my credentials because this will be distributed in the organization.


My connection string works fine because I tested it to bring a recordset into a worksheet.

I hope the question is clear! Thanks everyone.