mdb files opened in 2010 lose VBA code references

Question

Disclaimer: I just got back from some sick time, haven't seen the error myself or investigated the full range of possible causes, just looking for hints on what may be a systemic issue or something others have experienced in a similar situation.

Migrating the office to Office 2010 from Office 2003. Several critical Access Databases now give errors when opened in 2003 similar to what you'd get when you're using libraries you haven't set up properly in References (VBA app: Tools => References).

I'm investigating to see if the issue occurs when the files are opened in Access 2010 the same as when opened in 2003. Also, I'm trying to get a comparison to which libraries are referenced on machines that are working versus those that aren't. I'll update as I have more information.

In the meantime, is anybody aware of similar issues that may be addressed by a configuration change rather than a MDB edit?

Libraries referenced in 2010 system where MDB works:

• Visual Basic for Applications
• Microsoft Access 14.0 Object Library
• OLE Automation
• Microsoft ActiveX Data Objects 2.1 Library

The second library brings a question. The MDBs were originally written probably using Access 97, otherwise, using Access 2003, and so would have referenced the appropriate "Microsoft Access 12.0 Object Library" (for 2003) or similar. Will opening the file in 2010, as we see it's modified this reference to the 14.0 version, cause 2003 to look for that same version 14.0 library?

UPDATE: I've checked MDB files on my system, which does not appear to be having the issues (of course). The referenced libraries are the same in both Access 2003 and 2010 and the Microsoft Access ##.# Object Library reports 11.0 in 2003 and 14.0 in 2010, which should indicate it is not "hard set" in the code.

I will check the security options suggested by the commentor below and report back.

Answer 1

This does not jive 100% with what you are saying, but most of the problems I have when upgrading Office are due to the increased security settings introduced in Office 2007, which are the same for 2010.

I would first try going to File>Options>Trust Center>Trust Center Settings, and lower all the settings, even if just as a test. You can then ratchet them back up and see what breaks what. Rule this common problem out first, and then see if you still have actual coding issues.

Answer 2

See Errors using multiple versions of Access under Vista or Windows 7. Changing the registry permissions as per work around 1) fixed that exact problem for my system. I've been testing multiple versions of Access recently due to a bug in my Auto FE Updater utility.

Answer 3

The solution is this:

Access does not offer a way to opt out of the application check and reconfigure that is offered on Word and other Office apps. This is due to requirements of referenced libraries, which is where my particular symptoms came in.

If you are running multiple versions of Access, the version you ran last will always be the default version. Opening a different version manually will result in a reconfigure and a change in the default version.

In normal cases, when the application is functioning properly, these changes will not affect the function of databases so long as they are compatible with the version of Access set as default on the system. Different libraries will always be compatible at least back to the Access version they were written in.

In multi-version environments, making sure people use the normal method of selecting the database file via a file manager rather than opening Access directly and then selecting the desired file will result in a consistent version experience as well as minimal compatibility issues.

In this particular case, three users who were running the Office 2010 package customized for the first stage of our deployment process were experiencing the issue described above. Installing the Stage 2 version of Office 2010 reset the appropriate settings and resolved the issue.

UPDATE: I found one of the users having this issue did have a reference issue. Even after Office 2003 reconfigured itself on opening Access 2003, the references still included the Version 14.0 of Microsoft Access Object Library rather than version 11.0. This is what was causing the error messages in On Load and On Click events.