Skip to main content

Posts

Showing posts from 2018

Stop MS Access from prompting for passwords to linked ODBC tables.

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 passwordsCreate 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 DesignClose the Show Table dialogClick 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…