For people who have an account with Office365, remembering the account password is quite straightforward. Office365 has their own custom security and thus we get a different log in form when we open an Access database that depends on Office365 (whether as a published web database or containing linked tables to lists on the site):
As you can see on the screenshot, you can simply check the checkboxes, “Remember Me” and “Keep me signed in” if you don’t want to get prompted for password again. Simple!
Great! But I’m not on Office365…
When you’re using a SharePoint server that’s maybe hosted somewhere else or on your network, and SharePoint is using its default setting, you’ll see a login like this:
(on Windows XP)
(on Windows 7)
The important thing to note here is that it’s not an Access thing. Access is basically calling Internet Explorer to authenticate for you. So it’s the Internet Explorer setting we want to work with, and hence why you wouldn’t find anything to do with managing authentication within Access.
“Remember Me” doesn’t mean what it says
A more accurate statement would be probably “Remember Me, if you’re allowed to”. People are understandably frustrated when they check the box then next time they still get challenged. What’s going on? Well, in name of security, Internet Explorer may not be permitted to save the password unless you make some changes to the settings. By default, it tries to automatically authenicate for Intranet, which isn’t appropriate in this case. Therefore you need to change it so that it uses username/password. To do this, we start with….
Start Menu -> Control Panel -> Internet Options:
On the dialog, select Trusted sites zone, then click the Sites button
On the dialog, clear the checkbox for “Require server verification…” and then enter the host name of your SharePoint server. You don’t need to enter the full address of where your Access application is published to or linked to, just the base address. So if your Access application has a SharePoint list linked to http://mySharePoint.local/sites/MySite, then you would just enter “mySharePoint.local”
Click “Close” to return to Internet Properties dialog, then click Custom Level.
Scroll all way to the bottom then select “Authenicate with current username and password”
Once you’re set the radio button, click OK, then OK again on Internet Properties dialog to close both dialogs and save the changes. Back on Control Panel, we go to User Account.
Note: From this point on, the steps diverge between Windows XP and Windows 7 — for those on Windows 7 scroll down further to see the steps for Windows 7.
On the Advanced tab, click Manage Passwords
Click the “Add” button.
On this page, enter the same server host name that you entered for sites dialog previously, then the username and password you use to log in to your SharePoint host.
Click OK, then Close then OK. You’re done with establishing the identity. You can now go to your Access application, login in then check the “Remember Me” this time and you won’t be challenged next time you run your Access application.
For Windows 7 users
As mentioned the last part of saving passwords diverge when you get into User Accounts. In User Accounts, you would select Manage Credentials.
On Credential Manager, select Add Generic Credentials
You would then enter the same hostname of your SharePoint server that you entered in Sites for Trusted Sites and your username/password you use to log in.
At this point, you can now run your Access Application, log in and check the “Remember Me” and it’ll actually remember next time you run your Access application.
The steps I’ve outlined here are essentially the same steps that one would take to remember the login for any other SharePoint function. If you have your SharePoint server hosted somewhere other than Office365, they will likely have specific instructions that may differ slightly in allowing you to use “Remember Me” functionality. Thus, it may be good idea to consult with them first. Searching using “Remember Me SharePoint Internet Explorer” keywords usually will yield good results.
Furthermore, if you google a bit on the subject, you’ll find different methodologies. Some may recommend using Intranet zone instead of Trusted Sites zone in the Internet properties – there is indeed a subtle difference in behaviors between those two zones, so if you find the above instruction doesn’t work for you, give Intranet zone a try. This appears to be an version-specific issue for the Internet Explorer as well.
Can I use VBA to automate this?
This isn’t a final pronunciation but my guts says “No” on this subject. As mentioned, this is an Internet Explorer thing and Access needs to get the token after authentication, so while I believe there may be APIs that could allow you to simulate logging in, you’d be faced with problem of feeding that token to Access. One could argue this is a good thing since it makes it less likely that you’ll be hijacked and logged into someone else’s site. After all, saving passwords is a big enough security issue (e.g. it may mean someone could just waltz in, sit in any one of the workstation and they have access to the data). You could use the information above to at least get your users set up at the installation time, so it’ll be an one time thing.