1

System specs: 64-bit OS (Win7), 64-bit R (3.3.3), 32-bit MS Access (2016).

I have data in a 32-bit .accdb file and I want to read it into R. I tried this:

con <- odbc::dbConnect(odbc::odbc(),
                       dsn="MS Access Database")

but saw the following error:

Error: nanodbc/nanodbc.cpp:950: IM014: [Microsoft][ODBC Driver Manager] 
The specified DSN contains an architecture mismatch between the Driver and Application

Web search indicated that the bit difference between R and the database is the culprit. The default ODBC manager in Windows doesn't include drivers for MS Access (or rather, it seems to, but attempting to manage them using that tool gives you an architecture error). Following other advice, I used the ODBC manager for 32-bit programs (c:\windows\sysWOW64\odbcad32.exe) to create a new DSN with a new name for MS Access files, and then called this DSN:

con <- odbc::dbConnect(odbc::odbc(),
                       dsn="MSAccess32")

I got the same error, however, and suspect there is something I don't understand about what this error means. Is there a known workaround for the problem?

ErinMcJ
  • 593
  • 6
  • 20

1 Answers1

3

The access file itself knows nothing about bitness, its only about the client application and the bitness of the odbc driver:

If your R is 64 bit, you need the 64bit ODBC driver for access and therefore also use the odbc manager for 64bit, which is C:\Windows\System32\odbcad32.exe (in Win7 64bit).

While if your R is 32bit, you need the 32bit ODBC driver, located at C:\Windows\SysWOW64\odbcad32.exe.

You can download the required Access Database Engine 2010 Redistributable from here: https://www.microsoft.com/en-US/download/details.aspx?id=13255

So, download the 64 bit Access Database driver, create a 64bit DSN entry and you should be fine.

erg
  • 1,632
  • 1
  • 11
  • 23
  • This worked perfectly. Notes for future readers: (1) The linked 2010 64-bit driver does work for Office 2016. I installed it via the DOS command line with the /passive option, based on warnings elsewhere about driver conflict breaking the 32-bit version; I have encountered no problems so far. (2) In order to use the `DSN` parameter in `odbc` your DSN entry can't be generic, it has to point to your specific database file. Because existing DSNs were generic I assumed (wrongly) that the DSN was just for driver ID and that the db location would be specified using a separate function. – ErinMcJ Jul 13 '17 at 15:06