I'm using the following:
DoCmd.TransferDatabase acImport, "ODBC
Database", "ODBC;DSN=DSNname;SERVER=ServName;UID=userID;PWD
=pass" _ & "DATABASE=DBname",
acTable, "dbo.Table1", "Table2"  

On my computer where I have access to the database it's
working. I used different passwords and, surprisingly, it
works with any ones (I think because I have the access to
this DB).

But, when I'm doing this on another computer, which
doesn't have access to this DB, I have the error message:
Connection failed SQL State '2800'. SQL Server error: 18456
... Login failed for user 'userID'. The window appears to
enter a password for the Login 'userID'. I'm entering
there the same password, which is in the connection ODBC
string - "pass" and everything is going through.

How could I overcome it by using DoCmd.TransferDatabase...

Thanks