ExcelVBAとAccessの連携 第1回 Excelからデータベースへの接続

だいぶ前からまとめてみたいとは思っていたのですが、書きたいことのボリュームがどんどん増えてゆき、シリーズものになってしまいました…。できるだけ分かりやすく書けるようがんばります！

関連記事

• 第1回 Excelからデータベースへの接続 ←NOW!
• 第2回 テーブル設計とシート&コードの準備
• 第3回 SQLを使った読み書きの処理
• 第4回 条件を絞ってデータを読み込む
• 第5回 レコードの更新・削除
• 第6回 トランザクション処理
• 番外1 リファクタリングしたコード
• 番外2-1 プレースホルダを使ったSQL実行
• 番外2-2 続・プレースホルダを使ったSQL実行（汎用化）

この連載がもっと実用的なサンプルで書籍になりました！

連携させるメリット

小規模なデータ量ならExcelだけでもなんとかなるし、Accessだって単体でアプリケーションも作れます。が、敢えてAccessのデータベースをExcelから操作する（私にとっての）メリットについて。

グラフ化が容易

私は、出産前は検査の仕事をしておりまして、1/1000mm単位の膨大な量の測定データを毎日毎日Excelに溜め込んでいました。Excelにデータを格納すると、グラフのテンプレートさえ作っておけば、自動でグラフに描写されていくので便利なんです。

でも、データを溜めれば溜めるほどファイルサイズは大きく、動作も重くなる…。結局、ある程度の期間でファイルを新規にし、またそこへデータを溜めていく日々。どうなのこれ…。

そこで、

1. まずはExcelの測定用シート上にデータを入力
2. データをAccessのデータベースへ格納
3. データを見たいときはAccessからExcelのグラフ用シートへ読み込み

という方式へたどり着きました。Excel自体にデータは格納されないので、ファイルサイズは増えないし動きもサクサク。いろんな条件で絞り込んでデータを読み込めるから、前より便利になりました！

ExcelのVBAは勉強しやすい

MicroSoftOfficeにはどれもVBAが使えますが、ExcelVBAが一番ユーザーが多いんじゃないかな、と、思っています。

もちろんAccessも多いと思うんですが、使えるようになるまでの敷居がなかなか高く、ライトユーザーさんに敬遠される…(;´Д`)　Excelからだととっつきやすく、ユーザーが多いということは、それだけ勉強している人も多くて、ネット上でも情報が抱負です。

中小企業の強い味方

私の会社もそうですが、業務ソフトの自社開発は使用面でも金額面でも非常にメリットが大きいです。Excel＋Accessでかなり実用的なものもつくれちゃいます。

2016/6/29追記：このページをたくさんの方にご覧にいただいたおかげで、貴重な体験をさせていただきました…！　参考書選びの選択肢のひとつにしていただけたら光栄です(*´∀｀*)

Accessでテーブルを用意

というわけで、AccessではVBAを使わずに、データベースのテーブル設計だけやっておきます。

テーブルとは、こんな感じのものです。フィールドの名前と型（数値とか、文字列とか）を指定しておいて、そこへレコードを登録していきます。

フィールドには必ず1つ以上のキーと呼ばれる重複しないデータの入るフィールドを設定する必要があります。

この例なら、男か女かではひとつのレコードを特定できませんよね。名前も、同じ人がいるかもしれないのでキーにはできません。必ずひとつでないとならないので、IDや番号などを設定することが多いです。

ADOとSQL文を使ってExcelから読み書き

データベースへ読み書きするには、SQLという言語を使います。ExcelVBAでSQL文はそのままでは認識できないので、string型で宣言した変数に文字列としてSQL文を書いておいて、それをADOというインタフェースを使ってAccessを操作する…という感じです。

ADOというのはActiveX Data Objectsの略で、Microsoftが提唱しているデータアクセスのための技術だとか。アプリケーション、言語などに依存しないため使い勝手がよいらしいです！

基本コード

参照設定は使わない方法で書いてみたので、できるだけ幅広い環境で動けばいいなと思ってます。

Sub sample()
  Dim DBpath As String 'ファイル名
  Dim adoCn As Object 'ADOコネクションオブジェクト
  Dim adoRs As Object 'ADOレコードセットオブジェクト
  Dim strSQL As String 'SQL文

  DBpath = "C:\sample.mdb" '接続するファイル（～2003）のフルパス
  'DBpath = "C:\sample.accdb" '接続するファイル（2007～）のフルパス
  Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
  Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
  adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Access（～2003）ファイルを開く
  'adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル（2007～）を開く


  strSQL = "ここにSQL文を入れます" 'SQL文をString形式になるように

  '書込・編集・削除の場合----------------------------------
  adoCn.Execute strSQL 'SQLを実行
  '--------------------------書込・編集・削除の場合ここまで

  '読込の場合----------------------------------------------
  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ

  '出力方法1-スタートのセルを指定して一気に貼り付け
  Range("A1").CopyFromRecordset adoRs

  '出力方法2-ひとつひとつ貼り付け
  i = 1 'スタート行
  Do Until adoRs.EOF 'レコードセットが終了するまで処理を繰り返す
    Cells(i, 1) = adoRs!フィールド名1
    Cells(i, 2) = adoRs!フィールド名2
    Cells(i, 3) = adoRs!フィールド名3
    i = i + 1 '行をカウントアップする
    adoRs.MoveNext '次のレコードに移動する
  Loop
  '--------------------------------------読込の場合ここまで


  adoRs.Close 'レコードセットのクローズ（読込した場合のみ）
  adoCn.Close 'コネクションのクローズ
  Set adoRs = Nothing 'オブジェクトの破棄
  Set adoCn = Nothing
End Sub

1. 宣言（2～5行）
2. 接続・セット（7～12行）
3. 処理（15～36行）
4. 切断（39～42行）

というのが一連の流れかなという感じです。処理部分（15～36行）だけ変えれば汎用的に使えそう。

7,8行目のAccessファイル名指定と、11,12行目のファイルを開くところですが、Accessのバージョンが2003までと2007以降で書き方が変わってきます。

Excelへの出力については、一気にズバっと貼り付けする方法と、カスタマイズしながら貼り付けできる（量が多いと遅そう）方法があるみたいですね。両方書いてみましたが、用途によって使い分ければ良さそうです。

  '出力方法2-ひとつひとつ貼り付け
  Dim outputCell as Range: outputCell = Range("A1") '基点セル指定
  Dim row As Integer: row = outputCell.Row '行取得
  Dim col As Integer: col = outputCell.Column '列取得
  Dim field As Object, i As Integer
  Do Until adoRs.EOF 'レコードセットが終了するまで処理を繰り返す
    i = 0
    For Each field In adoRs.Fields 'フィールドの数だけ繰り返す
      Cells(row, col + i) = adoRs(field.Name)
      i = i + 1
    Next
    row = row + 1 '行をカウントアップする
    adoRs.MoveNext '次のレコードに移動する
  Loop

次回は、具体的に「こんなモノをつくるぞ」という提示をして、実際にAccess＋Excelで動くプログラムを作って解説してみたいと思っています。よろしければお付き合いください。

• ExcelVBAとAccessの連携 第2回 テーブル設計とシート&コードの準備

参考

• ADOを使ってExcelからAccessデータを利用してみよう | Excel・Accessで楽々シゴト！INFITH VBA Lab