RでMicrosoft Accessのデータベースファイルを読込・操作する
変更履歴・概要
2024/9/10 初版
2023年5月に行った作業のメモをもとに整理したものです。それぞれの環境によって違いがあるかもしれませんが，ご容赦ください。
できるようになること
Microsoft Accessのデータベースファイル（.mdb）とRを接続し，Rからmdbデータベースのデータを読み込み，操作できるようになります。
データのインポートには色々なパッケージがありますが，最近はrioパッケージの利用がおすすめなようです。
https://cran.r-project.org/web/packages/rio/index.html
https://ides.hatenablog.com/entry/2022/12/31/170809
が，rioパッケージではmdbファイル（accdbも）は対応していません。
利用データ
「全国植生調査データベース(H12-R3年度版）」（環境省生物多様性センター）を利用しました。入手法等は下記HPをご覧ください。ここで用いている「T001調査地点一覧」テーブルは76,698行（地点），98列のデータです。
ドライバのインストール
ODBC(Open DataBase Connectivity)ドライバが必要です。Windowsではコントロールパネル>ODBCデータソース(64bit)で確認してください。Microsoft365をインストールしたPCではドライバもありました。ドライバが無かった別のPCには，以下のサイトからAccessDatabaseEngine_X64.exe（最新のRが64bit版なのでx64のみ）（英語版）をインストールしていますが，日本語版でも可能かもしれません。
https://www.microsoft.com/en-US/download/details.aspx?id=54920
おそらく以下のMicrosoft 365 Access Runtimeでも動作するように思われます（Office2013が入っていたPCでインストールを試みましたが，Office2013を削除する必要があるようで，試していません）。
RからAccessへの接続方法
RODBCパッケージ
最初に紹介するのはRODBCパッケージを利用する方法で，RからODBCドライバを経由してデータベースにアクセスできます。なお，odbcConnect()やodbcConnectAccess()は，ヘルプによると32bit英語ドライバでのみ動作し，64bitRでは利用できません。2007versionの関数では64bitのドライバ・Rから接続できます。
データベースとの接続（mdb.pathはデータベースファイルのパスを指定してください）
library(RODBC)
channel <- RODBC::odbcConnectAccess2007(mdb.path)
データベース内のテーブル一覧を表示
sqlTables(channel, tableType = "TABLE")$TABLE_NAME
テーブルの列名一覧を表示
sqlColumns(channel, "T001調査地点一覧")$COLUMN_NAME
テーブルのデータを表示
sqlFetch(channel, "T001調査地点一覧")
クエリを表示。SQLで記述します。
sqlQuery(channel, "SELECT * FROM T001調査地点一覧 WHERE 最高階層優占種名 = 'ブナ'")
データベースとの接続を終了
close(channel)
参考
https://rpubs.com/vermanica/SQL_finalProject_MicrosoftAccess
http://goldenstate.cocolog-nifty.com/blog/2018/01/raccessaccdb-73.html
DBIパッケージ
DBIパッケージは各種のデータベース（SQLite，MySQL，PostgreSQLなど）の接続に共通して必要な機能をまとめたもので，各種のデータベースを同じように操作することを可能にします。接続できるデータベースは以下のページを参考にしてください。Accessへの接続はodbcパッケージを利用します。
https://github.com/r-dbi/backends#readme
データベースとの接続（mdb.pathはデータベースファイルのパスを指定してください）
library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc::odbc(),
.connection_string =
paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)};",
"Dbq=",mdb.path,";"))
データベース内のテーブル一覧を表示
dbListTables(con)
テーブルの列名一覧を表示
dbListFields(con, "T001調査地点一覧")
テーブルのデータを表示
dbReadTable(con, "T001調査地点一覧")
クエリを表示。SQLで記述します。
dbGetQuery(con, "SELECT * FROM T001調査地点一覧 WHERE 最高階層優占種名 = 'ブナ'")
なお，上記のコードではSQLインジェクションの可能性があり，好ましくはありません。安全な実行にはいくつかの方法があるようで，例えばdbSendQuery()とdbBind()との併用があります。詳しくはhttps://solutions.posit.co/connections/db/best-practices/run-queries-safely/をご覧ください。
データベースとの接続を終了
dbDisconnect(con)
参考
https://solutions.posit.co/connections/db/
https://yutatoyama.github.io/note/intro_R_for_SQL.html
DBIパッケージがおすすめ
理由1：処理が速い
以下のように，処理速度が速いです。
テーブルの読み込み
microbenchmark::microbenchmark(
rodbc = RODBC::sqlFetch(channel, "T001調査地点一覧"),
odbcdbi = DBI::dbReadTable(con, "T001調査地点一覧"),
times=10
)
## Unit: seconds
## expr min lq mean median uq max neval
## rodbc 4.013608 4.233927 4.380515 4.329421 4.492695 5.097223 10
## odbcdbi 2.387743 2.638226 2.666604 2.656564 2.720131 2.886073 10
クエリの読み込み
microbenchmark::microbenchmark(
rodbc = RODBC::sqlQuery(channel, "SELECT * FROM T001調査地点一覧 WHERE 最高階層優占種名 = 'ブナ'"),
odbcdbi = DBI::dbGetQuery(con, "SELECT * FROM T001調査地点一覧 WHERE 最高階層優占種名 = 'ブナ'"),
times=10
)
## Unit: milliseconds
## expr min lq mean median uq max neval
## rodbc 183.2582 184.1323 186.9164 185.4226 189.3973 192.8481 10
## odbcdbi 124.7187 126.9905 131.8810 127.7988 133.4076 156.8472 10
理由2：dplyrでデータベースに接続できます
dbplyrパッケージを利用することで，Rのデータ処理では定番のdplyrパッケージでの処理コードをSQLに自動変換することができます。つまり，SQLを知らなくてもデータベースのデータを処理できるようになります[1]。
dplyrパッケージの説明はたくさんあるので，例えば以下のページを参考にしてください。
https://www.jaysong.net/tutorial/R/dplyr_intro.html
なお，dbplyrパッケージの読み込みは不要で，自動で認識されるようです。
library(tidyverse)
データの処理コードを作成します。
comm_db <- tbl(con, "T001調査地点一覧") %>%
group_by(最高階層優占種名) %>%
tally()
class(comm_db)
## [1] "tbl_ACCESS" "tbl_dbi" "tbl_sql" "tbl_lazy" "tbl"
SQLはshow_query()で確認できます。
comm_db %>% show_query()
## <SQL>
## SELECT `最高階層優占種名`, COUNT(*) AS `n`
## FROM `T001調査地点一覧`
## GROUP BY `最高階層優占種名`
データの取得はcollect()で行います。
comm <- comm_db %>% collect()
class(comm)
## [1] "tbl_df" "tbl" "data.frame"
中身をみてみましょう。
comm %>% arrange(-n) %>% head
## # A tibble: 6 × 2
## 最高階層優占種名 n
## <chr> <int>
## 1 "コナラ" 6121
## 2 "アカマツ" 4248
## 3 "ミズナラ" 3631
## 4 "" 3335
## 5 "ブナ" 2673
## 6 "ケヤキ" 2090
複数のテーブルを結合させる場合
dbplyr(dplyr)パッケージのjoin関数を利用し，2つのテーブルを結合したデータの取得を試みます。が，（私の環境では）エラーとなってデータを取得できません。エラー内容からは文字コード関連でトラブルのようです（odbcパッケージ（UTF-8）とAccess（Shift-JIS）とで文字コードが違っているのが原因）
dbplyr_join <- tbl(con, "T001調査地点一覧") %>%
left_join(tbl(con, "TM06統一凡例"), by="凡例コード")
dbplyr_join %>% show_query()
dbplyr_join %>% collect()
データをRへ読み込んでからjoinすることは可能です。
dbi_join <- dbReadTable(con, "T001調査地点一覧") %>%
left_join(dbReadTable(con, "TM06統一凡例"), by="凡例コード")
dbi_join %>%
group_by(大コード, 大区分, 中コード, 中区分) %>%
tally() %>%
arrange(-n) %>% head()
## # A tibble: 6 × 5
## # Groups: 大コード, 大区分, 中コード [6]
## 大コード 大区分 中コード 中区分 n
## <chr> <chr> <chr> <chr> <int>
## 1 41 落葉広葉樹二次林 01 コナラ群落 5081
## 2 22 落葉広葉樹二次林 01 ブナ－ミズナラ群落 3701
## 3 40 常緑広葉樹二次林 01 シイ・カシ二次林 3498
## 4 54 植林地 01 スギ・ヒノキ・サワラ植林 2857
## 5 42 常緑針葉樹二次林 01 アカマツ群落 2653
## 6 47 湿原・河川・池沼植生 04 ヨシクラス 2318
buna <- dbi_join %>%
filter(最高階層優占種名 == "ブナ") %>%
group_by(調査区分コード, 大コード, 大区分, 中コード, 中区分) %>%
tally()
head(buna)
## # A tibble: 6 × 6
## # Groups: 調査区分コード, 大コード, 大区分, 中コード [6]
## 調査区分コード 大コード 大区分 中コード 中区分 n
## <chr> <chr> <chr> <chr> <chr> <int>
## 1 01 05 亜高山帯針葉樹林 01 オオシラビソ群団 1
## 2 01 06 亜高山帯広葉樹林 01 ミドリユキザサ－ダケカンバ群団 1
## 3 01 11 落葉広葉樹林（日本海型） 01 チシマザサ－ブナ群団 267
## 4 01 13 落葉広葉樹林（太平洋型） 01 スズタケ－ブナ群団 78
## 5 01 13 落葉広葉樹林（太平洋型） 02 イヌブナ群落 2
## 6 01 14 冷温帯針葉樹林 02 ヒノキアスナロ群落 3
なお，SQLで記述してデータを取得することもできます（が，下のコードでは重複する列名がある場合，その次の処理をしようとすると列名の置換が必要だとエラーがでます）。
sql_join <- dbGetQuery(con,
"SELECT * FROM T001調査地点一覧
LEFT JOIN TM06統一凡例
ON T001調査地点一覧.凡例コード = TM06統一凡例.凡例コード")
参考
こんなこともできそうで，最高階層優占種がブナと報告されている地点を横軸に緯度，縦軸に海抜の二次元散布図にプロットした図，日本地図に重ねてみた図を作成してみました[2]。このような詳細な処理については，別の機会に順次説明を加えていきたいと思います。
tbl(con, "T001調査地点一覧") %>%
filter(最高階層優占種名 == "ブナ") %>% collect() %>%
drop_na(緯度, 海抜) %>% filter(緯度 != 0) %>%
mutate(lat=緯度 + 緯分/60 + 緯秒/3600) %>%
filter(str_detect(海抜, "-", negate=T)) %>%
mutate(
elev=case_when(
str_detect(海抜, "m") ~ str_remove_all(海抜, "m"),
str_detect(海抜, ",") ~ str_remove_all(海抜, ","),
str_detect(海抜, "\\'") ~ str_remove_all(海抜,"\\'"),
TRUE ~ 海抜 # 不要な文字列を除去
),
elev = as.numeric(elev) # 海抜の全角数値はNAとなる
) %>%
drop_na(elev) %>%
ggplot() +
geom_point(aes(x=lat,y=elev), color="green3") +
scale_x_continuous(limits=c(30,45), labels=scales::label_number(suffix="°N")) +
scale_y_continuous(labels=scales::label_number(big.mark=",", suffix="m")) +
labs(x="緯度", y="海抜") + theme_bw()
library(sf)
library(leaflet)
attr <- "<a href='http://maps.gsi.go.jp/development/ichiran.html' target='_blank'>地理院タイル</a>"
tbl(con, "T001調査地点一覧") %>%
filter(最高階層優占種名 == "ブナ") %>% collect() %>%
mutate(lat=緯度+緯分/60+緯秒/3600, lng=経度+経分/60+経秒/3600) %>%
drop_na(lat,lng) %>% filter(lat != 0) %>%
# 経緯度はJGD2000(EPSG：4612)に投影変換した座標
sf::st_as_sf(coords=c("lng","lat"), crs="EPSG:4612") %>%
# leafletパッケージではWGS84(EPSG:4326)を利用
# https://rstudio.github.io/leaflet/projections.html
sf::st_transform(crs="EPSG:4326")-> sp_point
m <- leaflet() %>%
addTiles("https://cyberjapandata.gsi.go.jp/xyz/blank/{z}/{x}/{y}.png", attribution = attr) %>%
addCircleMarkers(data=sp_point, radius=1, weight=2) %>%
setView(135, 35, zoom=5)
m
Session info
sessionInfo()
## R version 4.3.3 (2024-02-29 ucrt)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 19045)
##
## Matrix products: default
##
##
## locale:
## [1] LC_COLLATE=Japanese_Japan.utf8 LC_CTYPE=Japanese_Japan.utf8 LC_MONETARY=Japanese_Japan.utf8
## [4] LC_NUMERIC=C LC_TIME=Japanese_Japan.utf8
##
## time zone: Etc/GMT-9
## tzcode source: internal
##
## attached base packages:
## [1] stats graphics grDevices utils datasets methods base
##
## other attached packages:
## [1] leaflet_2.2.2 sf_1.0-16 lubridate_1.9.3 forcats_1.0.0 stringr_1.5.1 dplyr_1.1.4 purrr_1.0.2
## [8] readr_2.1.5 tidyr_1.3.1 tibble_3.2.1 ggplot2_3.5.1 tidyverse_2.0.0 odbc_1.4.2 DBI_1.2.2
## [15] RODBC_1.3-23 here_1.0.1 fs_1.6.4 rmarkdown_2.26
##
## loaded via a namespace (and not attached):
## [1] rstudioapi_0.16.0 jsonlite_1.8.8 magrittr_2.0.3 farver_2.1.1 vctrs_0.6.5
## [6] memoise_2.0.1 base64enc_0.1-3 terra_1.7-71 webshot_0.5.5 htmltools_0.5.8.1
## [11] usethis_2.2.3 curl_5.2.1 raster_3.6-26 TTR_0.24.4 KernSmooth_2.23-22
## [16] htmlwidgets_1.6.4 plyr_1.8.9 zoo_1.8-12 cachem_1.0.8 mime_0.12
## [21] lifecycle_1.0.4 pkgconfig_2.0.3 webshot2_0.1.1 Matrix_1.6-5 R6_2.5.1
## [26] fastmap_1.1.1 shiny_1.8.1.1 digest_0.6.34 colorspace_2.1-0 ps_1.7.6
## [31] rprojroot_2.0.4 leafem_0.2.3 pkgload_1.3.4 crosstalk_1.2.1 labeling_0.4.3
## [36] imputeTS_3.3 fansi_1.0.6 timechange_0.3.0 httr_1.4.7 compiler_4.3.3
## [41] microbenchmark_1.4.10 proxy_0.4-27 remotes_2.5.0 bit64_4.0.5 withr_3.0.0
## [46] tseries_0.10-56 highr_0.10 pkgbuild_1.4.4 protolite_2.3.0 stinepack_1.5
## [51] sessioninfo_1.2.2 classInt_0.4-10 chromote_0.3.1 tools_4.3.3 units_0.8-5
## [56] lmtest_0.9-40 quantmod_0.4.26 httpuv_1.6.15 nnet_7.3-19 glue_1.7.0
## [61] quadprog_1.5-8 nlme_3.1-164 promises_1.3.0 gridtext_0.1.5 grid_4.3.3
## [66] generics_0.1.3 gtable_0.3.5 tzdb_0.4.0 class_7.3-22 websocket_1.4.1
## [71] hms_1.1.3 sp_2.1-4 xml2_1.3.6 utf8_1.2.4 pillar_1.9.0
## [76] later_1.3.2 ggtext_0.1.2 lattice_0.22-5 bit_4.0.5 tidyselect_1.2.1
## [81] miniUI_0.1.1.1 knitr_1.46 urca_1.3-4 forecast_8.23.0 xfun_0.43
## [86] devtools_2.4.5 timeDate_4032.109 DT_0.33 stringi_1.8.3 ggmap_4.0.0
## [91] yaml_2.3.8 evaluate_0.23 codetools_0.2-19 cli_3.6.1 xtable_1.8-4
## [96] reticulate_1.35.0 processx_3.8.4 jquerylib_0.1.4 munsell_0.5.1 Rcpp_1.0.12
## [101] dbplyr_2.5.0 png_0.1-8 parallel_4.3.3 ellipsis_0.3.2 fracdiff_1.5-3
## [106] blob_1.2.4 jpeg_0.1-10 profvis_0.3.8 urlchecker_1.0.1 bitops_1.0-7
## [111] scales_1.3.0 xts_0.14.0 e1071_1.7-14 rlang_1.1.3
Discussion