R连接MS Access数据库时odbcConnectAccess报错，如何解决？

It looks like you’re hitting a common snag with the RODBC package and modern Access databases. The truncated error you’re seeing (odbcConnectAccess is only us...) almost certainly stems from two key issues: odbcConnectAccess() is built for older .mdb Access files, not the newer .accdb format you’re using, and it relies on legacy drivers that don’t support modern Access databases.

Here are straightforward fixes to get your Access data into R smoothly:

1. Use odbcConnectAccess2007() for .accdb Files (RODBC Package) #

If you want to stick with RODBC, swap out odbcConnectAccess() for odbcConnectAccess2007()—this function is specifically designed for .accdb databases. First, ensure you have the *Microsoft Access Driver (*.mdb, .accdb) installed (it’s included with most Office installations, but you can download it separately if missing).

Try this code:

# Load RODBC package
library(RODBC)

# Connect to your .accdb file
db_address <- "S:/LKF/Interdepartmental/DataGovernance/Tools/MM_Backend.accdb"
db <- odbcConnectAccess2007(db_address)

# Import a table into a DataFrame (replace with your table name)
df <- sqlFetch(db, "YourTableName")

# Don't forget to close the connection when done
odbcClose(db)

2. Switch to the Modern odbc Package (Cross-Platform Friendly) #

The odbc package is a more robust, cross-platform alternative to RODBC. It works on Windows, macOS, and Linux (with extra setup for non-Windows systems) and plays nicer with modern databases.

First, install and load the package:

install.packages("odbc")
library(odbc)

Then create a connection using the ACE driver:

# For Windows users:
conn <- dbConnect(
  odbc(),
  Driver = "Microsoft Access Driver (*.mdb, *.accdb)",
  DBQ = "S:/LKF/Interdepartmental/DataGovernance/Tools/MM_Backend.accdb"
)

# Import your table into a DataFrame
df <- dbReadTable(conn, "YourTableName")

# Close the connection
dbDisconnect(conn)

Quick Note for macOS/Linux Users: #

You’ll need to wrap the Microsoft ACE driver in Wine (for Linux) or use third-party tools like unixODBC to get it working. It’s a bit more involved, but fully doable if you need cross-platform support.

3. Verify Your ODBC Driver Installation #

If errors persist, double-check that the correct driver is installed:

• On Windows: Go to Control Panel > Administrative Tools > ODBC Data Sources (64-bit) and look for "Microsoft Access Driver (*.mdb, *.accdb)" in the Drivers tab. If it’s missing, download the Microsoft Access Database Engine 2016 Redistributable (pick the 64-bit version if you’re using 64-bit R).

Once your connection is working, you can create your DataFrame and start plotting with packages like ggplot2 just like any other R data!

内容的提问来源于stack exchange，提问作者Ibo