[R] odbcConnectAccess2007 errors with Access databases on new PC

[Marc Schwartz]

On Feb 2, 2015, at 12:00 PM, utz.ryan <utz.ryan at gmail.com> wrote:
> 
> Hello,
> 
> I've connected R to Microsoft Access databases for years now
> using odbcConnectAccess2007. I recently got a new computer and R is
> absolutely refusing to connect to any Access database with the following
> error message:
> 
> Warning messages:
> 1: In odbcDriverConnect(con, ...) :
>  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
> Manager] Data source name not found and no default driver specified
> 2: In odbcDriverConnect(con, ...) : ODBC connection failed
> 
> It's definitely not a path name problem-I've checked a dozen times. A few
> things online have mentioned something about 32-bit and 64-bit systems
> causing problems. I've tried opening both the 64-bit and 32-bit versions of
> R with zero luck. My Office is running a 32-bit system.
> 
> Is there anything else I can try? I really would hate to lose the ability
> to connect R to my Access databases due to some intractable problem.
> 
> Thanks,
> Ryan


Take a look at the RODBC vignette:

  vignette("RODBC")

or

  http://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf

and see the footnote (16) at the bottom of page 22 regarding the creation of 32 bit DSNs and the following from page 20:

"32-bit Windows drivers for Access 2007 and Excel 2007 are bundled with Office 2007 but can be installed separately via the installer AccessDatabaseEngine.exe available from http://www.microsoft.com/en-us/download/details.aspx?id=23734."


The entire tool chain needs to be of the same architecture. So 32 bit Office, 32 bit ODBC drivers, 32 bit DSN and 32 bit R.

BTW, as you may be aware, there is a DB SIG list specifically for these types of questions:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

Regards,

Marc Schwartz