ODBC drivers not found

2
votes

I would like to open a 2007 Access database in R. To do so I am using the following code:

RIFSdatabase <- odbcConnectAccess2007(paste(db.dir, DB.filename, sep='/'))

If ran in 32 bit R (R console or R studio) the following error is returned:

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

If ran in 64 bit R (R console or Rstudio) the following error is returned:

Warning messages: 1: In odbcDriverConnect(con, ...) : [RODBC] ERROR: state IM003, code 160, message Specified driver could not be loaded due to system error 126: The specified module could not be found. (Microsoft Access Driver (*.mdb, *.accdb), C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEODBC.DLL). 2: In odbcDriverConnect(con, ...) : ODBC connection failed

MS Access Database drivers (*.mdb, *accdb) are listed as User DSN but...if I click configure I get the error message:

"The setup routines for the Microsoft Access Driver (*mdb, *accdb) ODBC driver could not be loaded to system error code 126: The specified module could not be found. (C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEODBC.DLL)

How can I fix this connection error?

I have not used my ODBC drivers on this computer for anything else - based on these errors are they perhaps not installed (despite being listed as User DSN)?

I do not care if I run R in 32 or 64 bit.

1
rms-accessodbcrodbc
Have you ran the office repair? It's possible your install has some issues that need to be fixed. Also, if you have 32 bit office installed you need to run the 32 bit R to use the ODBC drivers. There is a work around here if interested: stackoverflow.com/a/39962110/3242130 – manotheshark
With RODBC, run the command odbcDataSources() to see all available ODBC sources. Also, try the generalized connection method and not the 2007 one: odbcDriverConnect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Path\\To\\Database\\File.accdb') – Parfait
@Parfait - When I run the command obdcDataSources() I see MS Access Database "Microsoft Access Driver (.mdb, *.accdb)" listed. Using the command odbcDriverConnect('Driver={Microsoft Access Driver (.mdb, .accdb)};DBQ=x:/PATHWAY.accdb') I get the error In odbcDriverConnect("Driver={Microsoft Access Driver (.mdb, *.accdb)};DBQ=x:/surface/UOP/01_Data/BCSA_PRP_Group/BCSA_Database_GUI/BCSA_ExternalAccess.accdb") : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified – Alicia Ritzenthaler
Check if you actual call is like your post here with missing asterisk in front of .mdb? Should read odbcDriverConnect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};.. – Parfait
* is present when ran in R (just reran to confirm...same error) – Alicia Ritzenthaler

1 Answers

0
votes

Performing a google search with your error code showed the follow answer to a post with the same error on Microsoft:

You need to go to

C:\Windows\SysWOW64\obdcad32.exe

that will load the 32 bit odbc manager which is needed for creating DSN's with 32 bit drivers.

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_other/microsoft-access-driver-mdb-accdb-system-error/def76bd8-ffc6-44ec-a3e4-47a839b1314e