Debugging Access “Permission Denied” Errors

Thu 11 Jan 2007

One of my clients had a customer with server somewhere in rural Victoria.  A user (let’s call her Sue) had been running an MS Access 2003 application for years when it suddenly started throwing “Permission Denied” errors.

The Problem

Most of the application worked just fine.  On each form, Sue could navigate through the underlying recordset via the navigation keys; so there was no problem accessing the data.  However, when she tried to select a record via a combobox, she received the “Permission Denied” error.

Typical Access form equipped with a combobox to quickly select a record.
Typical Access form equipped with a combobox to quickly select a record.

My client had checked all obvious user login and file access permisssions.  All seemed in order.  He then asked me to have a look at it.

Investigation

My first check was the Access database permisssions. Nothing fancy there.  Any strange record locks? Nope.  Concurrency issues? Nope.

Interestingly, the application worked just fine when logged in as an administrator.  It was only when Sue tried to navigate via the combobox that there was a problem.

Debugging through the code revealed this in the combobox’s AfterUpdate event handler…

Private Sub cboSelect_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustomerId] = " & Str(Nz(Me![cboSelect], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

There must be 60 million copies of this code spread around the planet.  It is generated by the combobox wizard whenever a new combobox is dropped on a form and the developer selects the “Find a record…” option.  There can NOT be anything wrong with this code.  Each of us has done this about a thousand times.

This looks like a job for FileMon !

At this point, we started using FileMon to see which files were being accessed at the time the error occurred.  FileMon monitors and displays file system activity on a system in real-time.

We discovered that the Permission Denied error happened exactly when Access tried to read the DAO360.DLL.   In the code above, rs could be either a DAO or an ADO recordset, depending on which libraries are referenced.  In Sue’s application, DAO was used.

Normally, DAO360.DLL is stored in some common location where it is readable by all users.  Typically, C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.DLL.  However, on this server it was stored in C:\Documents and Settings\Tom\...

The Cause

Now we could see what had happened to “break” Sue’s application.  Tom had recently installed a new Access application which required an upgraded DAO.  Not being a system administrator, he had installed the DAO360.DLL in his own Documents and Settings folder.  Not surprisingly, Sue didn’t have permission to access anything in Tom’s Documents and Settings.

And when we checked with both Tom and Sue, the date of Tom’s installation was around the time Sue first noticed her problem.

The Solution

The solution was to use regsvr32 /u to unregister the DAO360.DLL.  We then moved it to a more accessible location and re-registered it (again, with regsvr32.)

Process Monitor

Note that on recent versions of Windows (Windows 2000 SP4, Windows XP SP2, Windows Server 2003 SP1, Windows Vista), Filemon and Regmon have been replaced by Process Monitor.  This was released in November 2006.

9 Responses to “Debugging Access “Permission Denied” Errors”

  1. Dugie Says:

    Nice! Filemon saves the day again, and again, and…. :)

  2. Dave A Says:

    Nice work Mike! That would have taken me a year to work out. (maybe it took you that long too, you didn’t say :) )

  3. MikeFitz Says:

    Dugie: I bet you’ve used FileMon a few times.

    Dave: It was all over in a couple of hours. It was my client who was the expert on FileMon but I’ve learnt how useful it can be.

  4. ashleigh Says:

    Hi Mike

    Those utilities from Sysinternals (now Microsoft) are a godsend, I’ve used FileMon, RegMon, PortMon and others, many times to try and figure out what’s going on when wierd things happen.

    These days I just have the set I commonly might need downloaded and ready to go, at home, at work, and anywhere else I have to futz about with somebodies PC.

    And all free!

    A good bit of detective work you’ve done there.

  5. MikeFitz Says:

    Ashleigh: Great idea. I’m going to load them on to that usb thingy on my keyring right now.

  6. John Graham Says:

    Thankyou very much for this tip. I was pulling my hair out over this one.
    We have a thin client environment and the same errors when users try to run the Wizards in MS Access. Had been working fine until recently, I followed your steps and it solved the problem.

  7. MikeFitz Says:

    G’day John, I’m glad I was able to help.

    Yes, I should have pointed out that my environment was also a thin client, and both users were sharing the same server.

  8. Danno Says:

    Been trying to work it out all day. Two minutes with this and it was solved.

  9. Sending email from access to outlook...gives permission denied Says:

    […] this out: http://mike.brisgeek.com/2007/01/11/…denied-errors/ is it possible that a dll library you need is stored in a different location than usual, like what […]

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Comment moderation is in use. Please do not submit your comment twice -- it will appear shortly.
"SEO"-type comments will be reported to Akismet as Spam.