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 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.

About Security

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.

References/Hattip

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

Comments

Attachment Reminder - and more for MS Outlook

I just did it again. We don't like to admit it, but we all have. You write a long letter describing the attachment, press send and then 10 seconds later remember you didn't actually attach the message. I finally decided to do something about it. Turns out it isn't too hard. Chiefly because Jimmy Peña at Code for Excel and Outlook already did all the hard work of writing up an excellent MS Outlook Etiquette Check Macro that does all the dirty work for you. What's left for you to do? In MS Outlook go to Tools > Macros > Visual Basic Editor Under the Project Panel (far left) Browse to Project1 > Microsoft Office Outlook Objects > ThisOutlookSession Double-click ThisOutlookSesson to Open (if you haven't been here before this will be a big blank canvas) Visit Code for Excel and Outlook Etiquette Check Code and select "Copy to Clipboard" at the top of the code. Or you can also copy from the code I've modified below if you prefer....

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects. It looks like it'll be as easy Apache finally. That is until you try to redirect a querystring. Then everything bombs. Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect. And How. In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content. Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console. In the HTTP Redirect Dialog: Check Redirect requests to this destination Enter your new path ending with $V$Q. e.g. http://mydomain.com$V$Q Counter-intuitively check Redirect all request to exact destination (instead of relative destination) Choose the appropriate Status Code (Permanent or Temp...

ArcGIS One-to-Many Labeling

ArcGIS is just plain lousy at dealing with any relationship that isn't one-to-one. We all have a slew of hacks just to deal with this limitation. I for one regularly am creating temporary cross-tab queries so I can represent multiple sample results at a collection point, fish surveyed at a cross-section and a host of other relationships. The classic example for mapping comes from the cadastral community and condominium lots. It's an odd situation where more than one person has title to the same piece of ground. How do you represent this? I've got a new trick thanks to Mohammed Hoque's article in ArcUser Magazine. We're going to do a database query inside a label expression, loop through the results and output the entire list to label. For our example we'll use Outfitting Areas in Idaho and we'll label them with the Outfitters and Guide License Numbers and Outfitter Names. 1.) Open ArcGIS and add your spatial layer with the unique identifier shared with...

oneidía

Search This Blog