Some users unable to run any VBA code (Access 2003)

[nim81]

Hi, wondering if anyone can offer any help on why this error message is triggered by trying to run any VBA code with Access 2003 running on Windows 10:

The expression On Click you entered as the event property setting produced the following error: Object or class does not support the set of events

This has started happening in the last few weeks with two users at my company. It doesn't appear linked to a particular database file - I can create a blank db, new form with one button and the following code, which causes the error:

Private Sub Command0_Click()
    MsgBox "test"
End Sub

I have around 20 usres on machines which all run on Windows 10 - this is so far affecting 2 of them. The same users can use the same file on different machines with no problem. Additionally, different users can log on to the affected machines and use them with no problem - seems only certain combinations of users and machines that get the error.

Already tried the following:
- Checked for broken references, nothing is missing
- Removed and reinstalled Office 2003

I should also point out the machines also have Office 2016 installed. I know it can be problematic to have two different versions installed, however to me it wouldn't explain why there were no errors previously, and why other users can use the same machine without problems.

Thanks for any help anyone is able to offer

 

[gemma-the-husky]

sounds like a "references" issue

I have A2016 and A2003 installed on the same machine, and when I restart I often have to repair A2003 to get to use the correct references.


When you say there are no broken references, what references is it actually showing?


eg, I just opened A2003 to see, and the reference is 16.0 object library - which isn't right, so I will now do a repair. Which I just did, and now it's back to 11.0 object library.

 

[nim81]

sounds like a "references" issue

I have A2016 and A2003 installed on the same machine, and when I restart I often have to repair A2003 to get to use the correct references.

I agree it sounds like a references issue, however what's strange in this instance is that there are no references missing, and they are using the correct version for 2003 (Access 11.0 Object Library). Repair doesn't seem to be having any effect either

 

[gemma-the-husky]

I am puzzled then. I just edited my last. Try repair, rather than re-install. Out of interest, when I reinstall, I haver to ignore a couple of warnings about FoxPro. Do you get those?


My full default list of references when A2003 is working correctly are these, in this order.

VBA
11.0 Object Library
OLE
DAO 3.6
ActiveX Data Objects 2.5

 

[nim81]

I don't get any warnings whilst installing, no, everything appears to install correctly. List of references is the same as yours and also the same as my own machine which works correctly (and also has both Office 2003 and 2016)

Really frustrating one I've just noticed that the affected computer also had the Windows 10 apps (or whatever MS is calling them nowadays) version of Office aswell, that's the one difference I can find so far

Are you aware of any way to *completely* remove Office from the OS (without wiping the PC)? It obviously seems to leave some traces of the old installation related to settings etc

Another theory I had was that the user of these two PCs clicked "No" at the question about "Do you want to allow unsafe expressions to run?" the first time they logged on to the PC, although that wouldn't explain why it only just started playing up recently

 

[gemma-the-husky]

You have to click YES to "unsafe expressions". It's advising you there is code in the database, and asking for permission to run the code.

do they have runtime, or full access? Full I presume as you can view the refereces. Make sure the database is in a trusted location, and try setting macro security to low. (even though it says not recommended. )

They aren't opening it with A2016 (which would update the refertences and then going back to A2003, are they?


they haven't installed Office 64 bit have they? not sure whether a 32bit/64 bit conflict would produce this error.

 

[nim81]

Good question, hadn't thought about the 32/64 bit thing, although as far as I remember I don't think it even lets you install mismatching versions

It's possible they could be mistakenly trying to open it in A2016 first and then opening it up in 2003 (as I know one of these users does use 2016 for some things), although if this were the case I'd expect to see the wrong version of the object library in the reference list

It's full version of Access and macros set to Low.

The user says it was definitely working about 3 weeks ago and apparently stopped working after Windows had gone through the "Installing updates" screen when booting Windows, which makes me a little nervous that I will be having this issue on more machines shortly...

 

[The_Doc_Man]

From the comments, this is obviously a shared DB. So how are people gaining access to the files? (A) via a folder with blanket MODIFY or (b) do you give individuals permission or (c) do you have a group with MODIFY permissions and add people to the group?

Using Windows explorer, have the afflicted users navigate to the folder where your app is located and right-click on the back-end file to get Properties >> Permissions. See what effective permissions are displayed for that user and compare that to someone who has normal access. I would have expected a different error than that for permissions, but this is an easy test that would rule out some issues quickly and doesn't break anything.

You should ALSO be able to look at the references list to get actual file names. You can check permissions on those files in the same way.

The thing I am thinking is that three factors can cause selective behavior. References (which are stored in the registry), a bad installation of Access on the user's system, and user permission sets. You re-installed Access so I would tend to discard that reason. You are checking references so that seems unlikely. But permissions can be insidious and nobody has brought up that chance. Windows patches HAVE been known to "adjust" permissions or to adjust behavior of libraries that run into permission issues.

 

[nim81]

Thanks for the reply. The file which we use Access 2003 for is on a shared drive - however it's a red herring, as even creating a blank MDB on the user's local PC, with nothing in it but a form with a button that fires a MsgBox will cause the error

I have reinstalled Access, but I believe it doesn't clear everything that is installed in the registry, as when running Access for the first time since resintalling I don't get asked, for example, the question about allowing Unsafe Expressions, and the Macro security setting is as it was prior to uninstallation.

I'm not an expert on where I should be looking in the registry, but the keys in HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Access are slightly different on my own machine than they are to the one with problems - the faulty machine has a "Spelling" key and it doesn't have the "Security" key

 

[gemma-the-husky]

Possibly reinstalling A2003 Over A2016 is not a good idea.

It tends to be the other way round - install A2016, retaining all previous versions.

To go back to my observation, when I have the problem with A2003, and I try open A2003, it sometimes reinstalls. That doesn't fix it, and I have to explicitly repair the A2003 version.

What you COULD try, is to create an MDE in A2003, and release that rather than the MDB. The references will not get updated in an MDE, and, A2016 will run an mde with no issues.

 

[Mark_]

Registry is the first place I'd look, but it seems you've already checked.

This sounds like the old DOS "Path" issues where one users login gives the wrong path for DLLs. I'd check in that direction to see if there is something about their network login that sets them up a little differently.