Friday, July 27, 2007

Living with Access 2007: "Disabled Mode", Trust Center, and a Bug

I have to live with Access 2007 at work. It's a mixed bag. Some things from Access 2003 have been improved. On the other hand, I despise the ribbon bar. Other things have not been touched: Access tables links still use path names (argghhhh), links still break when paths change, scripted column values pass the script rather than the script result (produces very bad side-effects), you still can't link in a query so that you could use the query results, there are no "view" equivalents, etc, etc.

Some things are really nasty. Here's one from an unanswered question on an Access site:

Re: Query Access 2007 "Disbled Mode"

... When I run the Make Table Query it says in the lower left corner the following error:

"The action or event has been blocked by Disabled Mode".

Access 2003 and 2007 alike have this idiotic question you have to answer on startup -- something about enabling "risky" stuff. I can't figure out how to disable the question. I think it's related to this problem. In my case I needed to write the output of a script from an Access 2003 formatted file to a new Access 2007 formatted file.

So how does one fix this? A member of the borg tells one how to discover disabled mode status programmatically, but that's no help. This probably helps:

http://blogs.msdn.com/access/archive/2005/11/01/488022.aspx

... Disabled mode and the Office Trust Center are designed to make it easier for a user to make trust decisions in scenarios where scripts that launch Access do not come into play.

There are scenarios, where developers of a solution want to ensure that code in Access (startup form/ macro or otherwise) always executes. In such cases the recommended approach is to ensure that one or more of the following conditions are met:

  • The database is signed with a trusted certificate.
  • The database is installed in a trusted location.

By meeting these conditions, the code within the solution will always be enabled. In scenarios where neither of these conditions can be guaranteed, Access can be made to revert to its legacy behavior of a modal startup trust prompt, that will launch and execute code in the database or not open the file at all. To revert to this legacy behavior set the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\

Value: ModalTrustDecisionOnly = 1 (DWORD)

Okay, let's look into the trusted location:

Click the Microsoft Office Button Button image, and then click Access Options...

Click Trust Center, click Trust Center Settings, and then click Trusted Locations.

...If you want to create a trusted location that is not local to your computer, select the Allow trusted locations on my network (not recommended) check box.

Click Add new location.

 ... We recommended that you don't make your entire Documents or My Documents folder a trusted location. Doing so creates a larger target for a hacker to potentially exploit and increases your security risk. Create a subfolder within Documents or My Documents, and make only that folder a trusted location.

In the Path box, type the name of the folder that you want to use as a trusted location, or click Browse to locate the folder.

If you want to include subfolders as trusted locations, select the Subfolders of this location are also trusted check box...

So I did this and got the error message: "the path you have entered is not a valid location or cannot be used ...". I think I'm getting this because I use drive letter substitution to get around the age-old access problem of linked file path dependencies.

A Google search on the last suggests the same problem arises with network drives, despite the claim that Access will allow trusted access to network drives. So this is probably a bug.

So I gave up, changed my trust center settings to "show the message bar", exited Access 2007, changed the default file mode to Access 2002-2003, restarted, clicked "enable everything" and I was able to run the query.

4 comments:

  1. THANK YOU, THANK YOU, THANK YOU!!!

    Another "gotta have it yesterday" mirrical delivered after another Windows registry hack.

    Once upon a time, I enjoyed using Access as the front-end to "big databases" like Oracle, Sybase and SQL Server. Not sure this is true anymore ...

    ReplyDelete
  2. Thanks! You saved me from hair tearing frustration! I appreciate it!

    ReplyDelete
  3. You saved my sanity!!! THANKYOU

    ReplyDelete
  4. Thanks! I went through several other steps, but messages were not available. Your suggestion is the only one that worked, so I am now able to run an Update Query without the error blocking me.

    ReplyDelete