Moving to Access 2007

Office 2007 has generally received a lukewarm response, but mainstream support for Office 2003 is set to end in April 2009, so remember the Boy Scout motto of "Be Prepared".

To date, I have had three customers that upgraded a single PC to 2007, while the rest of the users remained with 2003. Two have since downgraded back to 2003 due mostly to issues with Access 2007 compatibility. Some users prefer the new Ribbon interface as compared to the old-style menus and toolbars, but others, especially power users, feel lost. But I digress.

Upgrading an older-version Access database for use with 2007 is very much like what happened years ago when upgrading from the 16-bit Access 2.0 to the 32-bit versions such as Access 95/97 or 2000+. Many issues cannot be resolved using the new version - in some cases 2007 cannot open the database at all, so you will need to retain at least one PC with Access 2000-2003 in order to make the necessary changes.

There are a few things that are not supported, like using ENVIRON() expressions for default values in tables, but the most critical changes relate to the VBA environment, especially when using code-behind with forms and reports. Unlike the prior 32-bit versions, Access 2007 apparently examines all modules upon startup, so any code artifacts or compile errors can prevent opening the VB project, and thus the MDB cannot be opened. In fact, a 2000-2002 format file can compile successfully but then crash when opened using 2007. The only good news is that 2007 will tell you which module it can't load, so that gives you a starting point. But none of the VBA code will run until that module is fixed, and don't be surprised if there is more than one module that needs rework.

In a previous post, I described the workaround for that scenario - open the MDB with 2000-2003, export and delete the code module, compact & repair, then reopen with 2000-2003 and re-import the code module, compact & repair, and then it will probably work with 2007 as expected.

In the past, it has been possible to share a 2000-format MDB with users having Access 2000, 2002, and 2003 without conflict. In fact, I once had a single test PC with four versions of Access installed - 2.0, 97, 2000, and 2002 - but I would not attempt this with 2007.

The bottom line: I strongly recommmend that when moving to Access 2007, all users must have 2007 (only) installed to prevent corruption and/or data loss.