Office 2010 (especially 64-bit version) represents a major upgrade from 2007—Microsoft’s new investments ensure that even veteran Office 2007 users will gain significant benefits by upgrading to Office 2010.  As a consultant software developer with several MS Office based solutions in situ, I decided to upgrade early to get a headstart on making our software fully compatible with the new Windows/ Office.  My first foray into Office 2010 involved the 32-bit BETA version in a virtual-machine sandbox.  Current experiences are with the full, official 64-bit version; running in my production environment with all the data, deadlines and details of my regular work.

Pros

• Outlook 2010: Cleaner, simpler user interface—more practical and business-like.
• Access 2010: Compelling new features, including auto-complete intellisense in query design view.  Access 2010 supports table-based triggers (“data macros”), which are a good idea in principle but not directly upgradeable to SQL Server triggers.  The MS Access platform is now more user-friendly, consistent and ergonomic; and perhaps also more stable.
• 64-bit Office: >2 GB files, except for in MS Access files: I tested Access 2010 64-bit to its limits with some scientific data and found that Access 2010 64-bit still has a 2GB file size limit.  VBA7 has 64-bit arithmetic datatypes, but the Access database engine does not yet have native formats for 64-bit integer columns.  If you require 64-bit arithmetic or addressing in MS Office e.g. for scientific purposes, 64-bit SQL Server interfaced via ODBC from 64-bit VBA7 is probably the right solution for you (see first two comments at foot of this article); otherwise you should take a closer look at Excel 2010 to see if that will meet your needs.
• Software developers should migrate a little earlier than their clients (migrating to a 64-bit environment means you can test for 64-bit compatibility, and you can get the best of both worlds by running a 32-bit testing environment inside Microsoft Windows Virtual PC.)  However, beware of upgrading to Access 2010 for your development platform if you must deploy to Access 2007, and if you might perform “compact and repair” operations during development (there are glitches that affect backward-compatibility in this case—a possible work-around if you encounter this problem is to create an .accdb in Access 2007 and import everything into that).
• Obsolete features: Goodbye to “Data Access Pages” and other obsolete features – Microsoft has simplified the Office ecosystem by removing various obsolete features.
• Incompatible with many 32-bit addins/ macros etc. (Advantageous because this incompatibility provides additional protection against potentially unwanted software.)

Cons

• Incompatible with many 32-bit addins/ macros etc. (If you care, you can still run Office 32-bit inside a Windows XP virtual machine running in parallel with your main Windows/ Office installation.)
• 64-bit Office installation process: Installation as an upgrade on an old computer can be problematic, especially if that computer has previously had a smell of any 32-bit edition of Office. (My own efforts hit a road-block so tough I had to reinstall Windows afresh.)
• VBA 7 64-bit: Any Declare statements in your VBA code will need to be reviewed and specially annotated with a new “PtrSafe” 64-bit compatibility flag in order for your code to work:
  • Working with VBA in Office 2010 (32-bit) and Office 2010 (64-bit)
  • Compatibility Between the 32-bit and 64-bit Versions of Office 2010
  • Use the Office Code Compatibility Inspector (OCCI) from Microsoft, an automated tool for ensuring that your code is compatible with the Office 2010 64-bit object model.  Caveats: OCCI only works for Word, Excel, Powerpoint (the OCCI Developer tab addon is not integrated with other Office applications; however, copying VBA code for API calls from Access into Word enabled me to work around this limitation of OCCI).  OCCI is a simplistic tool that performs only a cursory inspection of the most obvious compatibility issues.  While this is very useful at the beginning of the migration process, there may be no way to avoid the need for true in-house expertise for performing the actual migration.
• VBA/ActiveX Calendar Control (a popular component) has been removed from MS Office.  In the 32-bit version of Office 2010, you can work around this by patching in mscal.ocx on each workstation that requires it; but in Office 2010 64-bit your only potential option is a more laborious reworking of the original project.
• COM controls (mscomctl.ocx) such as TreeView are no longer available in 64-bit editions of Microsoft Office.  This is apparently because COM is based on deprecated 32-bit code which Microsoft chosen not to fully upgrade (instead preferring to encourage migration to newer code libraries.)  Applications requiring certain COM controls may therefore require additional work to ensure a successful migration.
• Outlook 2010: Can stall/ crash during the initial configuration process for adding IMAP email accounts with more than a few hundreds of megabytes of data stored on the server.  This may be due to new default settings introduced by MS in their general upgrade to IMAP functionality.  I resolved this issue by loading a small IMAP account first and changing its settings so that only headers [rather than entire messages] are downloaded by default (after this, the larger accounts were configured without problems.)

Overall impressions – General system philosophy

Office 2010 is user-friendly (at least for end-users and for developers who are deploying to a pure Office 2010 environment), and builds on the all traditional strengths of MS Office.  The Office suite still suffers from a toy-town approach to some potentially productive new features – for example, MS went to all the trouble of creating a NEW data trigger development environment within Access WITHOUT making it directly compatible with preexisting equivalent RDBMS functionality.  However, there are many serious new features in MS Office that cannot be ignored by software developers or competitors – I personally use LibreOffice in parallel with MS Office, e.g. for the excellent LibreOffice “Draw”, and I still find the added value of MS Office to be totally worthwhile.

Owners of new computers running Windows 7 64-bit should install Office 2010 64-bit if wanting extra protection from pestilent software companies invading your MS Office installation with their company-branded toolbar, or if there is any prospect of requiring >2GB files or >32 bit arithmetic precision.  If you are upgrading from previous 32-bit Office installations without reinstalling Windows, or if you are running 32-bit Windows or Windows XP 64-bit, or if you require any 32-bit addons or compatibility with the old MS ActiveX Calendar Control; you should install the 32-bit version.  Microsoft’s own guidance can be found via this link.