2

I am using 32-bit R with RStudio to try and connect to an .accdb file. I have 32-bit MS Access running on my machine.

R version: 

platform       i386-w64-mingw32            
arch           i386                        
os             mingw32                     
system         i386, mingw32               
status                                     
major          3                           
minor          1.2                         
...
language       R                           
version.string R version 3.1.2 (2014-10-31)
nickname       Pumpkin Helmet

I'm trying to use the RODBC package to connect to an MS Access database. I cannot use odbcConnectAccess("location_of_database.accdb") to connect to a database provided by a client. This particular database was created with Access 2013. When I try to connect, I get a variety of warning messages:

Warning messages:
1: In odbcDriverConnect(con, ...) :
  [RODBC] ERROR: state HY000, code -1028, message [Microsoft][ODBC Microsoft     Access Driver] Cannot open database '(unknown)'.  It may not be a database that     your application recognizes, or the file may be corrupt.
2: closing unused RODBC handle 12 
3: In odbcDriverConnect(con, ...) :
  [RODBC] ERROR: state 01000, code 1, message [Microsoft][ODBC Microsoft Access Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x11a4 Thread 0x798 DBC 0x88e0064 Jet'.
4: In odbcDriverConnect(con, ...) :
  [RODBC] ERROR: state 01000, code 1, message [Microsoft][ODBC Microsoft Access Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x11a4 Thread 0x798 DBC 0x88e0064 Jet'.
5: In odbcDriverConnect(con, ...) : ODBC connection failed

Interestingly, I can use odbcConnectAccess2007() to connect successfully to databases that were created with Access 2010; however, when I try to use odbcConnectAccess() to connect to those same databases, I get the same errors above.

A Google search of the Error: state 01000, code 1 message above suggests that this page may be helpful, but even after following the steps to alter the HKEY_LOCAL_MACHINE does not work.

While I originally thought that this question was promising, the warning message there relates to "ACE DSN" while the error messages I'm seeing relate to "Jet DSN."

I cannot open the databases I'm trying to access with RODBC with my version of Access as the databases were created with Access 2013 and I have Access 2010 installed.

Can anyone help me trouble shoot why I can't open this database with odbcConnectAccess()?

Community
  • 1
  • 1
Steven
  • 3,238
  • 21
  • 50
  • Have you tried opening the Access_2013 database using your copy of Access_2010? What happens when you do? Also have you tried opening that database in R using `odbcConnectAccess2007()` instead of `odbcConnectAccess()`? – Gord Thompson May 10 '16 at 18:50
  • I get an error message that says `"Unrecognized database format [database location.accdb]."` – Steven May 10 '16 at 18:52
  • Have you tried opening that database in R using `odbcConnectAccess2007()` instead of `odbcConnectAccess()`? – Gord Thompson May 10 '16 at 19:08
  • Yes. I get the same errors as in my original question. – Steven May 10 '16 at 19:22
  • 1
    There is fundamentally no difference between Access 2007-2016 files as they all use same format. Certain UI features may differ even table elements but R connects to the ACE engine and not GUI. The *unrecognized database format* suggests corruption. Try creating a new Access .accdb and import all objects. – Parfait May 10 '16 at 19:51

1 Answers1

1

why I can't open this [Access 2013] database with odbcConnectAccess()?

I'm fairly certain that 

odbcConnectAccess(<fileSpec>)

is simply shorthand for

odbcDriverConnect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ=<fileSpec>')

(i.e., the older "Jet" ODBC driver), and

odbcConnectAccess2007(<fileSpec>)

is simply shorthand for

odbcDriverConnect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=<fileSpec>')

(i.e., the newer "ACE" ODBC driver).

The older "Jet" driver cannot work with .accdb files, so odbcConnectAccess2007() would be required. If that throws an "Unrecognized database format" error then it's likely that the database file is damaged.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418