Could open accdb database (MS Access) from R and now I can't

0
votes

I have a little R code that was working perfectly well until a few days ago and now, all of a sudden, it works on some PCs and it doesn't in others. Does anybody know if any update has been pushed by MS that could be causing the trouble?

I'm running R-32bit in all PCs and the ones not working seem to have upgraded MS Access 2010 to 2016.

This is my code:

library(RODBC)
testdb <- file.path("foo.accdb")
channel <- odbcConnectAccess2007(testdb)
tables_list=grep("foo_table", sqlTables(channel)[,3], value=TRUE) # list of tables with matching names   
odbcCloseAll()

The error message I get is:

Error in sqlTables(channel) : first argument is not an open RODBC channel

In addition: 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

1
rms-accessrodbc
Are you saying that all of the PC's in question have access to the same Access database? It's being stored on some sort of network drive then? and you're sure all of these different user accounts are granted permission to access the database? – Hack-R
It looks like an ODBC connection error. Test the ODBC connections to the Access Database. It may actually be a path error (a mapped drive may no longer be available). – SunKnight0
@Hack-R No, the file is copied into each of the PCs. – Floren
@SunKnight0 I checked that R can find the file. And the accdb is in the PC itself, not a shared drive. – Floren
You are getting an ODBC error which means that R is not looking for the file directly it is looking for an ODBC data source which in turn looks for the file itself. Therefore the ODBC data source R is looking for is either not there or is itself unable to find the Access file. Are you using a static ODBC data source? If yes, it is a 32-bit or a 64-bit? Does it pass connection test successfully? It is very likely that there is no problem at all within the R code itself. – SunKnight0

1 Answers

0
votes

Mystery solved! Apparently the upgrade to MS Access 2016 deleted the 32-bit drivers for ODBC. Reinstalling the Microsoft Access Database Engine 2010 32-bit got things to work again.

Thanks to all for your help!