My Access 97 backend/frontend database had over 700 objects and pretty complex reports and VBA code for an EDI translation application, so I was impressed when it only took me one afternoon to upgrade it to an Access 2007 database. First of all, after installing Access 2007, I downloaded and installed Office 2007 SP2 right way - I didn't even want to find out how an unpatched Microsoft product would behave. To upgrade an old database format, Access 2007 can automatically convert it for you to the new format, but you are likely going to have to do a few fixes yourself. The major caveats I encountered were:
* If you use DAO (data access objects) and try to add DAO to References in VBA, you can't - you will get a "Name conflicts with existing modules" error. Apparently, DAO is already built-in to Access, hence the "name conflicts" error when you try to add the DAO reference again. To make DAO work, change all "Dim r as Recordset" statements in your VBA code to "Dim r = DAO.Recordset". This can be easily done with find & replace.
* The "Cancel = True" statement in a BeforeUpdate event procedure causes "Property not found" error. Apparently, Cancel property no long exists in BeforeUpdate event even though the documentation says it does. The workaround is to replace "Cancel = True" with "Docmd.CancelEvent".
* The navigation pane in Access 2007 takes some getting used to. It shows all objects (table, queries, etc.) in one top-to-bottom list, so if you have hundreds of objects like I do, it can be a pain. It is always docked on the left side, and cannot be undocked. It spans from top to bottom, and its height cannot be adjusted, so it covers up major screen real estate. It can be collapsed or opened by pressing F11, but it is always on top of other windows. I hate to say it but this could be a deal-breaker for some people, in addition to the top ribbon bar that also annoys some users. Be sure to download a 30-day trial version of Access 2007 from Microsoft and try out the navigation pane yourself.
* If your Access 97 database has custom toolbars, Access 2007 will convert them also (!), but you won't be able to edit them as far as I can tell. The custom toolbars will be oddly placed under the "Add-ins" pull down menu. Clicking "Add-ins" will turn the entire ribbon bar into your custom toolbar(s). Another way is add your custom toolbars to the your "quick access toolbar" - go to Access options, Customize, All Commands, and find your custom toolbar name(s) to add. Again, I haven't found any way to edit custom toolbars, nor any way to create one in Access 2007.
* Compiling modules can sometimes crash Access 2007 - same thing can happen in Access 97. You just have to find the offending module(s) and recode or delete it/them.
* In the VBA window, you cannot compile individual module as in Access 97 - you must compile all modules in the database at once.
* In Datasheet view or Form view, if you press Ctrl-F to bring up the "Find" dialog, you won't see "Find first" like you do in Access 97. It is apparently gone. You can only "Find Next".
* Access 97 and 2007 can actually coexist; but opening Access 97 after you have installed Access 2007 will, for some reason, cause the Access 2007 reinstall dialog to come up the next time you open Access 2007.
* Not really a caveat, but in case you are too used to the unlimited installations of Office 97: You can only install Access 2007 on one PC with no hardware modifications, and you can only install on a different PC once every 120 days.