ExcelとAccessをVBAで連携しよう

2017/11/14

ExcelデータをAccessに保存しておくと何かと便利です。

データを共有できるし、データを他のソフトから参照もできます。（Excelファイルの中身も見れなくもないけど結構大変）

でも、Accessってあんまり利用されているところ見たことないですね。もっと積極的に使った方がいいと思います。Excelとの相性は良いですので。

[ad#top-1]

1 Accessファイルの用意
2 その前にExcel/VBAの参照設定
3 データベースを読み込む
4 フィルターをかけて特定の情報を得る方法
5 データを修正する
6 データを新規追加する

Accessファイルの用意

まずはAccessファイルを用意します。

今回の例は以下の通りです。名簿というテーブルを作って、名前、年齢、出身地というフィールドを作りました。データは10個入っています。

このデータを使ってExcelからアクセスしてみましょう。

その前にExcel/VBAの参照設定

AccessデータベースをExcel/VBAで使う場合、参照設定しておく必要があります。

Visual Basic Editorの「ツール」－「参照設定」を選択しましょう。

以下のウィンドウが現れます。リストの中から「Microsoft ActiveX Data Object X.X Library」にチェックを入れて、「OK」ボタンをクリックして閉じます。

バージョン番号（X.X）はどれでもいいですが、僕はいつも2.8を使っています。最新版という意味では6.1でもいいんだけど、バージョンNo.が飛んでいるからあえて使っていません。（たいした根拠はないです）

参照設定はこれで完了。

データベースを読み込む

こちらが読み込みのためのサンプルコードです。

Sub readDB()
  Set myCon = CreateObject("ADODB.Connection")
  myCon.Open _
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\...\Database1.accdb"
  Set myRS = CreateObject("ADODB.Recordset")
  myRS.Open "名簿", myCon

  r = 3
  Do While Not myRS.EOF
    Cells(r, 1) = myRS.Fields("名前").Value
    Cells(r, 2) = myRS.Fields("年齢").Value
    Cells(r, 3) = myRS.Fields("出身地").Value
    r = r + 1
    myRS.MoveNext
  Loop

  myRS.Close
  Set myRS = Nothing
  myCon.Close
  Set myCon = Nothing
End Sub

これを実行するとご覧の通り、Accessファイルの名簿テーブルの中身が現れます。

フィルターをかけて特定の情報を得る方法

フィルターをかけることができます。

以下がソースコードです。(1)のところが追加されました。年齢40未満の人だけを抽出するという意味です。

Sub readDB()
  Set myCon = CreateObject("ADODB.Connection")
  myCon.Open _
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\...\Database1.accdb"
  Set myRS = CreateObject("ADODB.Recordset")
  myRS.Open "名簿", myCon
  myRS.Filter = "年齢<40" '・・・(1)

  r = 3
  Do While Not myRS.EOF
    Cells(r, 1) = myRS.Fields("名前").Value
    Cells(r, 2) = myRS.Fields("年齢").Value
    Cells(r, 3) = myRS.Fields("出身地").Value
    r = r + 1
    myRS.MoveNext
  Loop

  myRS.Close
  Set myRS = Nothing
  myCon.Close
  Set myCon = Nothing
End Sub

結果はごらんのとおり、40歳未満の人だけ抽出できました。

データを修正する

今度はデータ保存の方です。特定のデータを修正する方法です。

次のソースコードは、「池田次郎」の年齢を66歳、出身地をアメリカに修正する方法です。

Sub updateDB()
  Set myCon = CreateObject("ADODB.Connection")
  myCon.Open _
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\...\Database1.accdb"
  Set myRS = CreateObject("ADODB.Recordset")
  myRS.Open "名簿", myCon, adOpenForwordOnly, adLockOptimistic
  myRS.Filter = "名前='池田次郎'" '・・・(1)

  myRS.Fields("年齢") = 66 '・・・(2)
  myRS.Fields("出身地") = "アメリカ" '・・・(2)

  myRS.Update '・・・(3)
  myRS.Close
  Set myRS = Nothing
  myCon.Close
  Set myCon = Nothing
End Sub

(1)でフィルターをかけておきます。これをやらないと全てのデータを修正してしまう危険があるので注意しましょう。(2)でデータを書き換えて、(3)でデータベースに更新しています。

データを改めて結果はこちら。確かに池田次郎さんのデータが書き換わっていることがわかります。

Accessファイルもこの通り。

データを新規追加する

次に新規追加です。

データを新規追加する場合、まず最初に行を追加します。①のmyRS.AddNewがそれにあたります。

Sub insertDB()
  Set myCon = CreateObject("ADODB.Connection")
  myCon.Open _
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\...\Database1.accdb"
  Set myRS = CreateObject("ADODB.Recordset")
  myRS.Open "名簿", myCon, adOpenForwordOnly, adLockOptimistic

  myRS.AddNew '...(1)
  myRS.Fields("名前") = "宮崎洋一"
  myRS.Fields("年齢") = 99
  myRS.Fields("出身地") = "熊本県"

  myRS.Update
  myRS.Close
  Set myRS = Nothing
  myCon.Close
  Set myCon = Nothing
End Sub

データを読み込むとこの通り最後に追加されていることがわかります。

Accessファイルもこの通り。

[ad#ad-1]

- Excel, VBA