通过DBI从Access到R的连接 [英] Connection from Access to R via DBI

问题描述

我希望分析便携式计算机上Access数据库中包含的R中的数据(使用dplyr). (我第一次尝试在R中建立数据库连接.)

I am looking to analyse data in R (using dplyr) contained in an Access database on my laptop. (My first time trying to set up a database connection in R.)

在tidyverse站点上，为了使dplyr处理Access数据，似乎连接必须通过DBI包(而不是RODBC)进行.

Looking at the tidyverse site, for dplyr to work on the Access data, it seems that the connection must be via the DBI package (rather than RODBC).

我正在努力使用dbConnect的语法.

I'm struggling with the syntax of dbConnect.

我的RODBC代码是

base1<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=[filepath]/AdventureWorks DW 2012.accdb")

我的DBI(失败)尝试是

My (failed) attempt for DBI is

DB <- dbConnect(drv=Microsoft Access Driver (*.mdb, *.accdb)), host=[filepath]/AdventureWorks DW 2012.accdb)

我在做什么错了?

(我在Windows 10上工作-都是64位.)

(I'm working on Windows 10 - everything 64 bit.)

推荐答案

我最近需要将RODBC定义的数据库连接转换为等效的DBI连接.这是原始的RODBC函数:

I recently needed to convert my RODBC defined db connections to equivalent DBI connections. Here's the original RODBC function:

connect_to_access_rodbc <- function(db_file_path) {
   require(RODBC)
   # make sure that the file exists before attempting to connect
   if (!file.exists(db_file_path)) {
     stop("DB file does not exist at ", db_file_path)
   } 

   # Assemble connection strings
   dbq_string <- paste0("DBQ=", db_file_path)
   driver_string <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
   db_connect_string <- paste0(driver_string, dbq_string)

   myconn <- odbcDriverConnect(db_connect_string)

   return(myconn)
}

如此处所述，dbplyr软件包是从DBI软件包构建的. DBI::dbConnect()的第一个参数必须是适当的后端驱动程序.请参阅链接以获取驱动程序列表.对于Access，odbc::odbc()驱动程序是合适的. dbConnect函数的第二个参数是上一个odbcDriverConnect调用中使用的完整连接字符串.考虑到这一点，以下功能应连接到您的访问数据库:

As explained here, the dbplyr package is built from the DBI package. The first argument of the DBI::dbConnect() must be an appropriate back-end driver. See the link for a list of drivers. For Access, the odbc::odbc() driver is suitable. The second argument the dbConnect function is the full connection string as used in the previous odbcDriverConnect call. With that in mind the following function should connect to your access database:

connect_to_access_dbi <- function(db_file_path)  {
  require(DBI)
  # make sure that the file exists before attempting to connect
   if (!file.exists(db_file_path)) {
     stop("DB file does not exist at ", db_file_path)
   }
  # Assemble connection strings
  dbq_string <- paste0("DBQ=", db_file_path)
  driver_string <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
  db_connect_string <- paste0(driver_string, dbq_string)

  myconn <- dbConnect(odbc::odbc(),
                      .connection_string = db_connect_string)
  return(myconn)
}

odbc软件包文档还提供了一个更细微的示例: https://github.com/r-dbi/odbc#odbc

The odbc package documentation presents a more nuanced example as well: https://github.com/r-dbi/odbc#odbc

这篇关于通过DBI从Access到R的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！