VBAからのデータベースへの接続方法は主に以下の3種類です。(MSクエリ操作は除きます。)
ここからはVBAから「Microsoft Query」等を除いて直接データベースのレコード(フィールド)を操作する場合の方法について説明します。
「Microsoft Query」についてはマクロの記録でコードが取得できるので、そちらを参考にして下さい。
| 名称 | 概要 |
|---|---|
| Microsoft Data Access Object(DAO) | Microsoft Access(Microsoft Jet)のデータベースエンジンを直接取り扱います。単一システムか小規模でローカルなシステムに向くものとされているようです。 MDBであればデータベースファイルの作成からテーブルやインデックスの作成などをVBA側から行なえます。これはAccessを含まないMicrosoft OfficeやMicrosoft Excel単体の環境でも可能です。 現在ではデータアクセスはADOが標準になっていますが、MDB環境操作(MDB自体やテーブルの作成等)についてに限ってはDAOの方が便利です。 MDB自体は古くからあるものですが Office2007以降でも問題なく扱えます。 当サイトでは以前はMDBは「Microsoft DAO 3.6 Object Library」を参照設定、ACCDBは「Microsoft Office 1x.0 Access Database Engine Object Library」を参照設定すると説明していました。 ですが、32ビット版、64ビット版の動作確認でMDBとACCDBのどちらも「Microsoft Office 1x.0 Access Database Engine Object Library」の参照設定で済むことが判りました。 Excel単体版等で「Microsoft Office 1x.0 Access Database Engine Object Library」が参照設定リストにない場合は「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」をマイクロソフトサイトからダウンロードさせてインストールして下さい。 |
| Microsoft Remote Data Object(RDO) | Excel97時代からあるODBC(Microsoft Open Database Connectivity)でのデータベース接続を標準的に取り扱うもので、古くは「業界標準のインターフェイス」とされていたものです。現在ではMicrosoftからもサポート終了となっており、ADOに取って替わっています。 既にこのRDOで作成されているマクロのメンテを行なうような目的であれば別ですが、RDO自体を新規に学ぶ必要はないと思います。 現在のWindows、Office環境では「Microsoft Remote Data Object 2.0」が参照設定のリストにありませんから、 以前にRDOで作成されていたプログラムも動作できない状態です。 当サイトにも以前はRDOで作成したサンプルを掲載していましたが、削除しております。 |
| Microsoft Active Data Object(ADO) | RDOが「業界標準のインターフェイス」と唄われていたのに対し、ADOはむしろMicrosoftの独自の方式かも知れません。しかし、別名で「OLEDB」などと呼ばれ、簡単にアプリケーションに組み込めることを唄い文句にしたもののようです。
(今では「OLE」自体が既に「死語」かも知れません) 新規に学ぼうとする場合は、このADOに絞ってしまって良いと思います。 ACCDBについてもADOでは参照ライブラリの変更もなく、接続文字列も変更だけで対応できるはずです。 RDOと同様で、ODBCによる接続も可能なのですが、 Windows側でODBC設定を行なわなくても、VBA上での接続文字列でサーバ名、ユーザー、パスワードなどを指定すれば接続可能なので、他社製データベースプロダクトにも問題なく対応できるようです。 (他社データベースプロダクトに接続する場合はその「ドライバ」のインストールは必要です) MDB環境操作については、上記のDAOの他、ADOXというADOの拡張機能でも可能なのですが、一般情報が古く実情はDAOに及ばなくて「立ち消え」状態のように見えます。 |
このようなことから、これからExcelVBAでデータベースアクセスを学ぶという方は、ADOに絞って学ぶということで良いと思います。 接続文字列とSQL文の編集に若干の違いがあるものの、それ以外はどのデータベースエンジンを利用する場合でもほぼ同じです。
これから説明するサンプルのデータベース(MDB or ACCDB)です。
作成したサンプルのデータベースをAccessで開くと、このように表示されます。
ですが、このサイトでのサンプルにはAccessが必要なわけではありません。
(画像をクリックすると、このページのサンプルがダウンロードできます)
この画像をクリックしてダウンロードして解凍させると、次ページ以降で説明するサンプルと対象となるデータベースを作成する仕組みのExcelワークブック等が現われます。 データベースの作成方法はこのページ先頭のコラムの通りです。
この画像は「SampleCorp1.mdb(又はSampleCorp1.accdb)」をMicrosoft Accessで開いたところです。 左ペインには作成済みのテーブルが表示されていて、それぞれはローマ字名称なので内容は類推できると思いますが、 上から「部署マスタ(MST_BUSYO)」「配属マスタ(MST_HAIZOKU)」「社員マスタ(MST_SYAIN)」「役職マスタ(MST_YAKU)」となります。 右ペインはこの中で「社員マスタ(MST_SYAIN)」が開いたところになっています。
ダウンロードした中には、「MDB(ACCDB)テーブル定義(テーブル操作サンプル).xlsm」、 「MDB(ACCDB)インポートデータ(テーブル操作サンプル).xlsm」も含まれています。 これらのマクロから、このサンプルのデータベースの新規作成、サンプルデータのインポートを行なうことができます。
マクロから操作する場合のMDB(ACCDB)はMicrosoft Accessがインストールされていなくても動作できます。
次ページ(DAO)と、さらにその次ページ(ADO)は、ここで紹介しているサンプルデータベース「SampleCorp1.mdb(又はSampleCorp1.accdb)」から配属一覧を取得するもので「MDB(ACCDB)配属一覧.xlsm」として収録されています。
ここでメインとなる「配属マスタ(MST_HAIZOKU)」には社員単位の社員コード、配属された部署コード、役職コードが登録されていますが、 氏名、部署名、役職名のフィールドはありません。 参照する段階でこれらを各マスタから接続させて参照するというのが今回のサンプルです。
SQL文自体は2種類とも全く同じで、基本ベースは「配属マスタ(MST_HAIZOKU)」を参照するのですが、 このテーブルには名称のフィールドがありません。 そこで部署コードに対する部署名は「部署マスタ(MST_BUSYO)」から、 役職コードに対する役職名は「役職マスタ(MST_YAKU)」から、 社員コードに対する氏名、カナ氏名、性別、生年月日、入社日等は「社員マスタ(MST_SYAIN)」からと それぞれをJOINして参照するようなSQL文になっています。
マクロのサンプルは「MDB(ACCDB)配属一覧.xlsm」です。
この中にDAOのサンプルである「GetMDBDataByDAO」と、 ADOのサンプルである「GetMDBDataByADO」があります。
「MDB(ACCDB)配属一覧.xlsm」とサンプルデータベース「SampleCorp1.mdb(又はSampleCorp1.accdb)」は同じフォルダに配置すればマクロが実行できるようにしてあります。
2種類の方式の個々については次ページ以降で説明しますが、処理結果は、
このようにサンプルデータベース「SampleCorp1.mdb(又はSampleCorp1.accdb)」の内容が取り込まれます。
なお、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];
各フィールドIDの前のアルファベット1文字がテーブルを修飾しているIDで、 「H」が「配属マスタ(MST_HAIZOKU)」、 「B」が「部署マスタ(MST_BUSYO)」、 「Y」が「役職マスタ(MST_YAKU)」、 「S」が「社員マスタ(MST_SYAIN)」となります。 FROM句は3つのJOIN式で並列に参照させていますが、 MDBの場合はこのようにカッコを付けてやらないと機能しません。 SQLServerならこのカッコはなくても機能します。
WHERE句は処理当日に在籍している社員という意味で入社日、退職日を判断させています。
ORDER BY句の並び順は部署コード、役職コード、社員コード順となります。