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

小さな会社のAccessデータベース作成・運用ガイド 2013/2010/2007対応 (Small Business Support)

posted with ヨメレバ

丸の内とら翔泳社 2013-07-23

続々ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本

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" '接続するファイルのフルパス
  Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
  Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
  adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Accessファイルを開く


  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

宣言（2～5行）
接続・セット（7～10行）
処理（13～34行）
切断（37～40行）

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

10行目、Accessファイルを開くところですが、上記はAccessのバージョンが2003までの対応で、2007以降は拡張子が.accdbで、ProviderをMicrosoft.Jet.OLEDB.4.0からMicrosoft.ACE.OLEDB.12.0へ変更が必要なようです。

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

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

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

参考

公開日：2013/03/07

更新日：2014/04/29

ExcelVBAに興味をお持ちの方は、入門記事もありますのでよろしければどうぞ！

この記事をご覧の方にオススメ

posted with ヨメレバ

立山秀利秀和システム 2014-03-25

入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス)

posted with ヨメレバ

立山秀利講談社 2012-04-20

アプリ作成で学ぶExcel VBAプログラミングユーザーフォーム&コントロール

posted with ヨメレバ

横山達大秀和システム 2012-03