I am in charge for migrating old Access 2007 project to MS SQL server 2008 Express. The first stage is to move all the data from MS Access database to SQL server while keeping the Access forms and reports at the client.
So, the data are now moved, an SQL server user (for accessing only that particular database) created, and the tables are linked the Access database via ODBC connection. However, there's one nuisance that should be somehow solved: the Access regularly asks for the user password, when opening the Access database.
The users on server PC and client PC both log onto their local machines, ie their users are not verified on independent domain server.
I see there are several ways how to solve this:
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 the program is closed:
SELECT 1
PWD=something;
in it.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 and hide the code by delivering a MDE or just password protecting the code.
You can find an explanation of this behaviour here.