3

I am trying to export a data frame from R to MS Access but it seems to me that there is no package available to do this task. Is there a way to export a data frame directly to Access? Any help will be greatly appreciated.

Saurabh Datta
  • 33
  • 2
  • 9
  • Your question doesn't look like related to programming directly, so please consider using another site for this – Stepan Novikov Oct 27 '17 at 13:34
  • Possible duplicate of [Getting data from dataframe to MS-access DB Target Tables using R](https://stackoverflow.com/questions/34001509/getting-data-from-dataframe-to-ms-access-db-target-tables-using-r) – Ben Jacobson Oct 27 '17 at 16:47

2 Answers2

3

The following works for medium sized datasets, but may fail if MyRdataFrame is too large for the 2GB limit of Access or conversion type errors.

 library(RODBC)

 db <- "C:Documents/PreviouslySavedBlank.accdb"

 Mycon <- odbcConnectAccess2007(db)

 sqlSave(Mycon, MyRdataFrame)
Duck Dodgers
  • 3,409
  • 8
  • 29
  • 43
A nonymous
  • 31
  • 2
  • this did not work for me because 1) my file path was too long, so I had to create a db connection first and 2) sqlSave gives an error that Argument 1 must have names – Nova Mar 27 '19 at 18:27
0

There is the ImportExport package.

The database has to already exist (at least in my case). So you have to create it first. It has to be a access database 2000 version with extension .mdb

Here is an example:

ImportExport::access_export("existing_databse.mdb",as.data.frame(your_R_data),
                            tablename="bob")

with "bob" the name of the table you want to create in the database. Choose your own name of course and it has to be a non already existing table

It will also add a first column called rownames which is just an index column

Note that creating a .accdb file and then changing the extension to .mdb wont work ^^ you really have to open it and save it as .mdb. I added as.data.frame() but if your data is already one then no need.

There might be a way for .accdb files using directly sqlSave (which is used internally by ImportExport) and specifying the driver from the RODBC package. This is in the link in the comment from @BenJacobson. But the solution above worked for me and it was only one line.

R. Prost
  • 1,958
  • 1
  • 16
  • 21