[Excel]AccessのテーブルをExcelに連携する。SQLで絞り込む。

Office365 BusinessでもAccessが使えるようになったのでエクセルで管理していたデータをAccessに移行しました。
Accessはあまり触れてこなかったので簡単な内容ではありますがデータ接続の方法をメモします。※Office2016でしか試してません。

やりたいこと

↓Accessのデータを

↓Excelで表示します。

やったこと

データタブのAccessデータベース接続からデフォルト設定で接続しました。

これでできました。思った以上に簡単でした。

ついでに

折角なので再取得ボタン用のマクロを作成しようと思います。
テーブル名を「Users」に変更して下記コードをボタンに登録します。

sample

Sub Sample()
    ThisWorkbook.Worksheets("Sheet1").ListObjects("Users").Refresh   
End Sub

データを再取得できました。簡単すぎます。

SQLも書いてみる。

[データ]-[接続]-[ブックの接続]で名前を確認します。

Databese1でしたのでConnectionsから接続文字列を変更します。

sample

Sub Sample()
    Dim sql As String
    sql = "SELECT FamilyName, FirstName FROM users WHERE Age >= 20"
    ThisWorkbook.Connections("Database1").OLEDBConnection.CommandText = sql

    ThisWorkbook.Worksheets("Sheet1").ListObjects("Users").Refresh    
End Sub

絞り込まめました。
もっと、こう、ForでまわしてRangeを指定してセルにセットして・・・、なんてことを考えていましたが予想以上に簡単でした。

そのほか

Accessを立ち上げていたり、Excel起動時にAccessを立ち上げたりすると共有エラーが出ることがありました。
ただこれは、デフォルトの接続設定ではアクセス許可が「Share Deny Write」になっていたためでした。
ReadやReadWrite、Share Deny Noneにすると解消されました。