我最近需要将我的RODBC定义的数据库连接转换为等效的DBI连接。

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)
}

如here所述，dbplyr程序包是从DBI程序包构建的。DBI::dbConnect()的第一个参数必须是适当的后端驱动程序。有关驱动程序列表，请参见链接。对于Access，odbc::odbc()驱动程序是合适的。dbConnect函数的第二个参数是在前面的odbcDriverConnect调用中使用的完整连接字符串。记住这一点，下面的函数应该连接到您的access数据库：

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

正如here所解释的，你仍然可以使用odbcConnectAccess2007()，但是需要here的32位MS Access 2007驱动程序。

我几天前才用过这个。

library(RODBC)

# for 32 bit windows
# Connect to Access db
# channel <- odbcConnectAccess("C:/Users/Excel/Desktop/Coding/Microsoft Access/Northwind.mdb")

# Get data
# data <- sqlQuery( channel , paste ("select * from Name_of_table_in_my_database"))

# for 64 bit windows
channel <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/Users/Excel/Desktop/Coding/Microsoft Access/Northwind.mdb")

data <- sqlQuery( channel , paste ("select * from CUSTOMERS"))

odbcCloseAll()