New PCs ship with Windows7, which offers 32-bit or 64-bit versions. 32-bit operating systems (all of them, not just Windows) are limited to only using 4GB of RAM - if you want more (and who wouldn't), you need the 64-bit version. 64-bit Windows 7 allows between 8 and 192GB of memory depending on which version you have (the current AMD64 chip set maximum is 256TB, but even I don't need that...)

If you're installing MS Office, you get to pick which version of Office you want - 32 or 64-bit. The default and recommended option is the 32-bit version, which runs just fine on both 32 and 64-bit windows.

So far, so good.

Then we get to MS Access, and things get a bit ugly:

• If you're only using Access as a stand-alone database, or with Access-to-Access connections (or Office-to-Access) then you're probably Ok.
• If you're trying to use Access as a database store for another application, connecting through ODBC, then you're life is about to get hard.

ODBC hell

The 32-bit versions of Access require the 32-bit ODBC drivers. Getting to these requires a small amount of digging, but it can be done (see [1]). Using them is more interesting.

If the application that's trying to get to the Access database is also 32-bit, then you might be Ok. You're stuck with 32-bit limitations and slowly diminishing support, but at least it works.

If the application uses Jet drivers, you might be Ok.

Otherwise you will be seeing this message a lot: "The specified DSN contains an architecture mismatch between the Driver and Application". Really a lot.

To resolve this, all the components in the chain of connection have to have the same bit-ness. The source application, the ODBC driver and the Access database. In practice, this means 32-bit (see [2]).

The Death of Access?

My clients use Access, so I need to retain it as a development environment, but I also use languages like Python and Perl where I want the current 64-bit versions. Until now, I have sometimes used Access as a temporary (or permanent) data store because I already have it and am familiar with the management interface. Not any more.

This interoperability problem is so annoying that I now try not to use Access at all unless I have to for a client project.

My default working database is now SQLite for small-scale projects and either MySQL or PostgreSQL for larger ones.

Bitter, bitter experience

This article was prompted by an unhappy experience with a client migrating a web application that was running under 32-bit Windows Server to 64-bit.

The application is in Perl and uses an Access database for storage. My first (and I thought natural) approach was to install the current (ie 64-bit) version of Perl, check that the scripts still compiled and that the configuration was matched to the new server names and that should be all. I get these optimistic thought sometimes.

The eventual solution was to uninstall 64-bit Perl and install the 32-bit version instead. The application now works, but I'm just that bit more cynical.

References

[1] Getting at the 32-bit ODBC driver [full Microsoft technet article]

[2] Why you probably have to use 32-bit Office [full Microsoft technet article]

The recommendations for which edition of Office 2010 to install are as follows:

• If users in your organisation depend on existing extensions to Office, such as ActiveX controls, third-party add-ins, in-house solutions built on previous versions of Office, or 32-bit versions of programs that interface directly with Office, we recommend that you install 32-bit (x86) Office 2010 (the default installation) on computers that are running both 32-bit and 64-bit supported Windows operating systems.
• If some users in your organisation are Excel expert users who work with complex Excel spreadsheets, they can install the 64-bit (x64) edition of Office 2010 to move beyond the 2 GB addressable memory boundary that is imposed on 32-bit editions. Note that this boundary differs from the physical (on disk) size of the spreadsheet on your computer's hard disk drive. It is possible to have an Excel file whose size on disk is smaller than 2 GB, but which when it is opened contains enough data to occupy 2 GB or more of addressable memory.
• In addition, if you have in-house solution developers, we recommend that those developers have access to the 64-bit edition of Office 2010 so that they can test and update your in-house solutions on the 64-bit edition of Office 2010.

Also: Choose the 32-bit or 64-bit version of Microsoft Office [full Microsoft article]

The 32-bit version of Office 2010 is the recommended option for most people, because it prevents potential compatibility issues with other 32-bit applications, specifically third-party add-ins that are available only for 32-bit operating systems.