データベースへの接続方法

VBAからのデータベースへの接続方法は主に以下の3種類です。(MSクエリ操作は除きます。)

VBAから「Microsoft Query」等を除いて直接レコード(フィールド)を操作する場合の方法について説明します。
Microsoft Query」についてはマクロの記録でコードが取得できるので、そちらを参考にして下さい。


名称 概要
Microsoft Data Access Object(DAO) Microsoft Access(Microsoft Jet)のデータベースエンジンを直接取り扱います。単一システムか小規模でローカルなシステムに向くものとされているようです。
MDBであればデータベースファイルの作成からテーブルやインデックスの作成などをVBA側から行なえます。これはAccessを含まないMicrosoft OfficeMicrosoft Excel単体の環境でも可能です。
現在ではデータアクセスはADOが標準になっていますが、MDB環境操作(MDB自体やテーブルの作成等)についてに限ってはDAOの方が便利です。

MDB自体はOffice2007以降でも問題なく扱えて、この場合は「Microsoft DAO 3.6 Object Library」を参照することになります。 一方、Office2007以降ではMDBではなく、ACCDBという形式が登場しています。 まだ、一般化していないようで参考情報が少ないようなのですが、コードの記述はあまり変わらずに利用できるようです。
本サイトでもMDB関連のページには最後の方に対応方法の説明を追記していますが、DAOの場合は 参照ライブラリが「Microsoft Office 1x.0 Access Database Engine Object Library」に変わります。

Microsoft Remote Data Object(RDO) Excel97時代からあるODBC(Microsoft Open Database Connectivity)でのデータベース接続を標準的に取り扱うもので、本来、「業界標準のインターフェイス」とされていたものです。現在はADOに取って替わっています。
既にこのRDOで作成されているマクロのメンテを行なうような目的であれば別ですが、RDO自体を新規に学ぶ必要はないと思います。
さらには、現在のWindows、Office環境では、「Microsoft Remote Data Object 2.0」が参照設定のリストにありませんから、 以前にRDOで作成されていたプログラムも動作できない状態です。 本サイトにも以前はRDOで作成したサンプルを掲載していましたが、削除しております。

Microsoft Active Data Object(ADO) RDOが「業界標準のインターフェイス」と唄われていたのに対し、ADOはむしろMicrosoftの独自の方式かも知れません。しかし、別名で「OLEDB」などと呼ばれ、簡単にアプリケーションに組み込めることを唄い文句にしたもののようです。 (今ではOLE自体が既に「死語」かも知れません)
既に最近のWindows標準(MDAC)や、Microsoft SQL Server,Microsoft AccessではADOを「標準」としているなのでDAO,RDOからは早々に移行される方が良いようです。
当然ながら、新規に学ぼうとする場合は、このADOに絞ってしまって良いと思います。
ACCDBについてもADOでは参照ライブラリの変更もなく、接続文字列も変更だけで対応できるはずです。
RDOと同様で、ODBCによる接続も可能なのですが、 Windows側でODBC設定を行なわなくても、VBA上での接続文字列でサーバ名、ユーザー、パスワードなどを指定すれば接続可能なので、他社製データベースプロダクトにも問題なく対応できるようです。 (他社データベースプロダクトに接続する場合はその「ドライバ」のインストールは必要です)

MDB環境操作については、上記のDAOの他、ADOXというADOの拡張機能でも可能なのですが、一般情報が古く実情はDAOに及ばなくて「立ち消え」状態のように見えます。


このようなことから、これからExcelVBAでデータベースアクセスを学ぶという方は、ADOに絞って学ぶということで良いと思います。 接続文字列とSQL文の編集に若干の違いがあるものの、それ以外はどのデータベースエンジンを利用する場合でもほぼ同じです。

これから説明するサンプルのデータベース(MDB)です。

サンプルデータベースのテーブル一覧
(画像をクリックすると、このページのサンプルがダウンロードできます)

この画像をクリックしてダウンロードして解凍させると、次ページ以降で説明するサンプルと対象となるデータベース(MDB)が現われます。
この画像は「SampleCorp1.mdb」をMicrosoft Accessで開いたところです。 左ペインには作成済みのテーブルが表示されていて、それぞれはローマ字名称なので内容は類推できると思いますが、 上から「部署マスタ(MST_BUSYO)」「配属マスタ(MST_HAIZOKU)」「社員マスタ(MST_SYAIN)」「役職マスタ(MST_YAKU)」となります。 右ペインはこの中で「社員マスタ(MST_SYAIN)」が開いたところになっています。




ダウンロードした中には、MDBテーブル定義(テーブル操作サンプル).xlsmMDBインポートデータ(テーブル操作サンプル).xlsmも含まれています。 これらのマクロから、このサンプルのデータベースの新規作成サンプルデータのインポートを行なうことができます。
マクロから操作する場合のMDBMicrosoft Accessがインストールされていなくても動作できます。




次ページ(DAO)と、さらにその次ページ(ADO)は、ここで紹介しているサンプルデータベース「SampleCorp1.mdb」から配属一覧を取得するもので「配属一覧.xlsm」として収録されています。




ここでメインとなる「配属マスタ(MST_HAIZOKU)」には社員単位の社員コード、配属された部署コード、役職コードが登録されていますが、 氏名、部署名、役職名のフィールドはありません。 参照する段階でこれらを各マスタから接続させて参照するというのが今回のサンプルです。




SQL文自体は2種類とも全く同じで、基本ベースは「配属マスタ(MST_HAIZOKU)」を参照するのですが、 このテーブルには名称のフィールドがありません。 そこで部署コードに対する部署名は「部署マスタ(MST_BUSYO)」から、 役職コードに対する役職名は「役職マスタ(MST_YAKU)」から、 社員コードに対する氏名、カナ氏名、性別、生年月日、入社日等は「社員マスタ(MST_SYAIN)」からと それぞれをJOINして参照するようなSQL文になっています。




マクロのサンプルは「配属一覧.xlsm」です。
この中にDAOのサンプルである「GetMDBDataByDAO」と、 ADOのサンプルである「GetMDBDataByADO」があります。
「配属一覧.xlsm」とサンプルデータベース「SampleCorp1.mdb」は同じフォルダに配置すればマクロが実行できるようにしてあります。




2種類の方式の個々については次ページ以降で説明しますが、処理結果は、

上のAccessのテーブルをExcelに取り込んだところ

このようにサンプルデータベース「SampleCorp1.mdb」の内容が取り込まれます。




なお、SQL文解説サイトではないので、ここではSQL文の構文解説は行ないませんが、概要だけ説明します。

SELECT H.[BUSYO_CD]
      ,B.[BUSYO_NM]
      ,H.[YAKU_CD]
      ,Y.[YAKU_NM]
      ,H.[SCD]
      ,S.[KANJI_SEI]+S.[KANJI_MEI]
      ,S.[KANA_SEI]+S.[KANA_MEI]
      ,S.[NYUSYA_YMD]
      ,S.[TAISYOKU_YMD]
FROM ((([MST_HAIZOKU] AS H
INNER JOIN [MST_SYAIN] AS S ON H.[SCD]=S.[SCD])
LEFT OUTER JOIN [MST_BUSYO] AS B ON H.[BUSYO_CD]=B.[BUSYO_CD])
LEFT OUTER JOIN [MST_YAKU] AS Y ON H.[YAKU_CD]=Y.[YAKU_CD])
WHERE S.[NYUSYA_YMD]<=#2017-01-01#
 AND (S.[TAISYOKU_YMD] IS NULL OR S.[TAISYOKU_YMD]>#2017-01-01#)
ORDER BY H.[BUSYO_CD],H.[YAKU_CD],H.[SCD];
SELECT句のフィールド配置は配属一覧シートの列配置と合わせてあります。 上の画面では列番号9の退職日だけが入り切れていません。
各フィールドIDの前のアルファベット1文字がテーブルを修飾しているIDで、 「H」が「配属マスタ(MST_HAIZOKU)」、 「B」が「部署マスタ(MST_BUSYO)」、 「Y」が「役職マスタ(MST_YAKU)」、 「S」が「社員マスタ(MST_SYAIN)」となります。 FROM句は3つのJOIN式で並列に参照させていますが、 MDBの場合はこのようにカッコを付けてやらないと機能しません。 SQLServerならこのカッコはなくても機能します。
WHERE句は処理当日に在籍している社員という意味で入社日、退職日を判断させています。
ORDER BY句の並び順は部署コード、役職コード、社員コード順となります。

Accessを持たない環境でMDB(ACCDB)を使いたい方へ   以下のようなツールをご用意していますので、合わせてご利用下さい。
MDB(ACCDB)生成/テーブル定義取得ツール」
  ⇒ワークシート上に登録したテーブル定義内容で実際にMDB(データベース)ファイルを作成したり、
    現存するMDB(データベース)ファイルの定義内容を取得するツールです。
MDB(ACCDB)データ取得ツール」
  ⇒SQL文の検証や、データの調査・修正を行なうツールです。