Access and SQL server

I have 02 different procedures I pasted below. My questions are:
1 - In which circumstances should I use
cn.ConnectionString = "DSN=MyDataSource;Uid=sa;Pwd="
 OR
cn.ConnectionString = "Provider=sqloledb;" & _
           "Data Source=computer1;" & _
           "Initial Catalog=pubs;" & _
            "User Id=sa;" & _
           "Password="

2 - when I use :cn.ConnectionString = "DSN=MyDataSource;Uid=sa;Pwd="
It works fine.
But When I use:
cn.ConnectionString = "Provider=sqloledb;" & _
           "Data Source=computer1;" & _
           "Initial Catalog=pubs;" & _
            "User Id=sa;" & _
           "Password="
It gives me an error logon failed for a user "sa" Reason not associated with SQL server Connection.
Even when I changed User Id= administrator it gives me the same error:logon failed for a user "administrator" Reason not associated with SQL server Connection.

Though I am logging as a domain administrator to the machine where MSAccess is installed and it is the same account I logged on to the domain controller where sql server is installed.

thanks








Private Sub Command5_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection

cn.ConnectionString = "DSN=MyDataSource;Uid=sa;Pwd="
         

cn.Open

strSQL = "Select * from authors where au_lname = 'Ringer' "

Set rs = cn.Execute(strSQL)

Do While Not rs.EOF
MsgBox (rs!au_lname & "   " & rs!au_ID)
rs.MoveNext
Loop

MsgBox ("END OF FILE NOW")

End Sub
****************************************************
Private Sub Command6_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=sqloledb;" & _
           "Data Source=computer1;" & _
           "Initial Catalog=pubs;" & _
            "User Id=sa;" & _
           "Password="

cn.Open

strSQL = "Select * from authors"

Set rs = cn.Execute(strSQL)
MsgBox (rs!au_lname)
Start Free Trial