This trick works for a domainless environment AND when you want to connect to SQL Azure tables from you Access Web App.
The best solution is obviously to use Windows security.
If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until Access is closed:
- copy the connect string of one of your tables
- create a passthru queries "ptqConnect" and enter any fast SQL statement in it, like
SELECT 1
- paste the connect string of into the PTQ Connect property, and make sure you add the
PWD=something; in it.
- in the startup procedure of your app make sure you call that PTQ. Something like
DCount("*", "ptqConnect") will do.
That's it. Since Access remembers opened connections until you close it, even if you close the db, your other tables will now open without any fuss, even if no password is stored in the linked tables Connect string.
If you don't want to expose the connection string that includes the PWD, you could as well initiate a connection from VBA Code and hide the code by delivering a ACCDE or just password protecting the code. The choice is all yours.
From here: http://stackoverflow.com/questions/9093266/save-password-for-odbc-connection-to-ms-sql-server-from-ms-access-2007