Connecting to MS Access databases with the 64-bit FDO ODBC provider

One of the (many) joys of going 64-bit is the astronomically huge amount of memory (2^64 bytes) you can throw at any application.

However, one of the pains thus far (on Windows at least) is the lack of connectivity to MS Access databases due to a lack of a fully native 64-bit ODBC driver.

Well it seems Microsoft have finally listened to its 64-bit userbase and finally released 64-bit drivers for Microsoft Access Databases last month.

Now how can we use these drivers in the just released 64-bit FDO Toolbox? If you thought the Connect to ODBC command choosing MsAccess as the data source type (like I first did), then you thought wrong.

It turns out the Office 2010 ODBC drivers have slightly tweaked the required connection string.

Here's what a current MS Access ODBC connection string would look like:

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\parcels.mdb

If you used that same connection string in a 64-bit FDO Toolbox, that will not work (you'll get an error message similar to: RDBMS: No open database). You have to tweak that string so it looks like this:

Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\parcels.mdb

That will load the correct 64-bit driver.

Now I had just made this discovery after releasing the first ever 64-bit FDO Toolbox, so in the meantime to connect to MS Access databases using the 64-bit FDO Provider, choose Connect to ODBC command using the Generic data source type.

This will present a simple UI which will allow you to enter the raw ODBC connection string.

And remember that FDO Toolbox is just another FDO client application, so this method should work for any other application that uses 64-bit FDO, like: AutoCAD Map 2011 and MapGuide Enterprise 2011