Skip to main content

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.

  1. Create a special type of query called a Pass-Through Query which does store passwords
  2. 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

  1. On the Ribbon choose the Create Tab and click Query Design
  2. Close the Show Table dialog
  3. Click the Pass-Through button on the Design Tab
  4. 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.
  5. In the SQL Query window put in a nonsense query like "SELECT 1".
  6. Save the Query, I'll call mine dbConnect.

Create a macro that runs at startup

  1. On the Ribbon choose Create Tab and click Macro
  2. 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.

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



        Popular posts from this blog

        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 to
        Pick the virtual directory you want to redirect. e.g. Click HTTP Redirect under IIS in the IIS management console.In the HTTP Redirect Dialog:
        Check Redirect requests to this destinationEnter your new path ending with $V$Q.  e.g.$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

        Maintaining Cross-Database Referential Integrity

        It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

        Common examples would include employees, counties, regions and other lookup tables.

        Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

        Here's a Solution
        For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

        Step 1:
        We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

        CREATE VIEW dbo.PIC_Sex AS SELECT SexID, Sex, Rank FROM Master.dbo.PIC_Sex ORDER BY Rank
        Yes, that's right, Male and Female! Slugs are awesome.

        Step 2:
        We create a new user-defined function xdf_Val…

        Serving up KML in IIS 6

        To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.

        Google Earth reads KML and KMZ files. The MIME type for KML files is

        * application/

        The MIME type for KMZ files is

        * application/

        Source: Google KML Tutorial

        To add a MIME type to a Web site or directory

        1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

        2. Click the HTTP Headers tab.

        3. Click MIME Types.

        4. Click New.

        5. In the Extension box, type the file name extension.

        6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

        To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…