Linked Tables: Remove Login Prompt

[q_test]

I have Access database with links to SQL Server using file DSN. It
propmts an error message everytime I open a table. The message is like:

The login failed. The trusted connection cannot be established.

I manually inserted the password (PWD) in the file DSN. But it always
thinks this is a trusted connection and ignore the login username and
password stored in DSN file.

How can I change in DSN so that the problem will be fixed? Thanks.

 

[Douglas J Steele]

Have you indicated in the DSN to use SQL Server authentication, not Windows
authentication?

 

[q_test]

I checked the radio with SQL Authentication.

However, in DSN, I don't see anything sepecific for this option value.

Do you know?

 

[Douglas J Steele]

The radio button was what I was talking about.

Why bother with a DSN, though? I show how to use DSN-less connections at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Rather than

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"

like the example has, you'd want

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
"Uid=" & UserName & ";Pwd=" & Password

(That implies you'd need to change the routine to accept UserName and
Password as well as DatabaseName and ServerName)