You should be using integrated security. But since we don't control all the things, sometimes database password authentication is forced on us.
You can set up a file DSN with the password, but in MS Access it is never saved. Instead everytime you reopen the database and view a table you see this:
Here is the workaround.
- Create a special type of query called a Pass-Through Query which does store passwords
- Create a macro that runs this query at startup
This works by initiating a special silent query at startup using the password. Once the database password has been used once, it is cached for the MS Access session.
Create the Pass-Through Query
- On the Ribbon choose the Create Tab and click Query Design
- Close the Show Table dialog
- Click the Pass-Through button on the Design Tab
- Click Property Sheet to edit the connection string properties. You can paste a connection string or use the ... builder to open a GUI. If you use the builder be sure to choose to Save the password in the connection string.
- In the SQL Query window put in a nonsense query like "SELECT 1".
- Save the Query, I'll call mine dbConnect.
Create a macro that runs at startup
- On the Ribbon choose Create Tab and click Macro
- Build a Macro which opens our new query.
Set the Action = OpenQuery and choose our dbConnect we created above.
Click Show All Actions in the Ribbon and then choose SetWarnings Turn Warnings On = No before the OpenQuery and back to Yes afterwards.
- Save your Macro as AutoExec. This is a special macro name that will run at startup.
Now close your database and reopen it. When opening linked tables you should no longer be prompted for a password.
In my experience, these types of passwords are the ones you find on sticky notes on monitors. They aren't real security because they are such a pain to enter.
That being said, embedding the ODBC connection password in your database makes the password knowable to anyone who can open the MS Access database. I would encourage you to place the MS Access database in a folder secured using Active Directory security to the correct group.
- The recipe for this solution was derived from Patrick Honorez's answer on Stack Overflow.
- Microsoft Support: How to create an SQL pass-through query in Access
- Microsoft Support: Create a macro that runs when you open a database
- Microsoft Community Question: How to get rid of prompts for macro action query for user