VBAからのデータベースアクセス

ExcelからAccessのデータベースを呼び込む

　VB 6.0には、データフォームウィザード、Data Environmentデザイナといった、データベースに関するツール類が豊富に用意されているが、残念ながらOffice付属のVBE（Visual Basic Editor）にはこういった機能はない。このため、VBAからADOを使うためには、自動化されていないぶん、それなりの知識が必要となってくる。知識といってもある程度“お約束事”ではあるので、まずはExcelからAccessのデータベースにアクセスするサンプルを見てみることにしよう。
　本サンプルでは、Officeをインストールした際に、インストールディレクトリ配下に展開される、「¥Office¥Samples¥Northwind.mdb」というサンプルデータベースを使用する。コマンドボタンを押した時に、社員テーブルを読み込み、1列目にフリガナ、2列目に氏名を表示するだけの簡単なプログラムである（図2）。社員テーブルの概要を図3に示す。要はこのテーブルからフリガナと名前を抜き出して表示するという、実にシンプルな作業だ。図4にこのプログラムの作成手順を説明しているので参考にしてほしい。

図2：Excel上のコマンドボタンを押すと、セルにデータが読み込まれる

図3：Northwind.mdbの社員データベーステーブル

図4：サンプルプログラムの作成手順
 
   
 
新規にBookを作成し、メニューから、［表示］-［ツールバー］-［コントロールツールボックス］を選択し、コントロールツールボックスを表示させる。

   
 
コントロールツールボックスから、コマンドボタンを選択し、シート上にコマンドボタンを貼り付ける。

   
 
貼り付けたコマンドボタンをダブルクリックすると、自動的にVisual Basic Editorが起動する。そこにコードを記入してゆく。

   
 
Visual Basic Editorのメニューから、［ツール］-［参照設定］を選択し、参照可能なライブラリ一覧より以下の4項目をチェックし［OK］ボタンをクリックする。
  • Microsoft ActiveX Data Objects（Multi-dementional）
  • Microsoft ActiveX Data Objects 2.1 Library
  • Microsoft ActiveX Data Objects 2.1 Recordset 2.0 Library
  • Microsoft ADO Ext. 2.1 DDL and Security
    ＊今回のサンプルでは、「Microsoft ActiveX Data Objects 2.1 Library」だけをチェックしても実行できる。

    •  
    自動的に「CommandButton1_Click()」が生成されているので、そこにリスト1のコードを記述する。注意して欲しいのは、dbcn.Openの行にある、Data Sourceの記述の部分だ。ここには、データソースとなるデータベースファイルを指定するので、指定するファイルの置き場所によって変更が必要だ。

    　Excelのシートの、コントロールツールボックスのデザインモードを解除するとコマンドボタンが押せるようになる。ではさっそく、コマンドボタンを押してみよう。図2のようになっただろうか？ このサンプルはとても簡単だが、これだけで十分ADOを使ったプログラムとして成立している。では実際の内容について細かく見てゆくことにしよう。

    ■ADOオブジェクトモデル

    　プログラムの説明に入る前に、ADOプログラミングの概略について触れておこう。ADOオブジェクトモデルには7つのオブジェクトと4つのコレクションが含まれており、この組み合わせによって、データアクセスの機能が実現できる。以下がその概略である（表1・2）。

    表1：ADOのオブジェクト

     オブジェクト

     機能

    Connectionデータソースとの接続に関するオブジェクト
    CommandSQLステートメントを組み込んでいるオブジェクト
    ParameterSQLステートメントのパラメータを組み込んでいるオブジェクト
    Recordsetデータの移動と操作などを行なうオブジェクト
    FieldRecordsetオブジェクトの列を組み込んでいるオブジェクト
    Error接続で発生したエラーを組み込んでいるオブジェクト
    PropertyADOオブジェクトの特徴を組み込んでいるオブジェクト

    表2：ADOのコレクション

     コレクション

     機能

    Errors接続で生じた単一の失敗の応答として発生する、すべてのErrorオブジェクト
    ParametersCommandオブジェクトに関連づけられている、すべてのParameterオブジェクト
    FieldsRecordsetオブジェクトに関連づけられている、すべてのFieldオブジェクト
    PropertiesConnection、Command、Recordset、Fieldの各オブジェクトに関連づけられている、すべてのPropertyオブジェクト

    　これらの関係は図5を参照されたい。

    図5：ADOオブジェクトの関係図

    　基本的に必要なオブジェクトは、Connection、Command、Recordsetの3つである。実際のプログラムの流れとしては、

    1. データベースとの接続（Connection）
    2. 実行するSQL文の設定（Command）
    3. レコード取得（Recordset）
    4. レコードセットオブジェクトの破棄（Recordset）
    5. 接続オブジェクトの破棄（Connection）
    といった具合に作成する。リスト1をざっと眺めてみると、上記の流れに沿って処理が作成されていることがおわかりいただけるだろう。

    リスト1：サンプルプログラム

    ■ADOによるデータベース接続

    　ADOを用いてデータベースに接続するには、Connectionオブジェクトを使用する。この接続先の指定には、2種類の方法がある。ひとつはリスト1で示している方法で、Providerなどのプロパティをひとつひとつ指定してやる方法、もうひとつは、DataLinkファイルをファイル指定で設定する方法だ。
    　実際には、DataLinkファイルを使用したほうがプログラムも簡素化でき、また実用的だろう。その理由は、パラメータとして設定しなければならないプロバイダなどの文字列に何を使用すべきかが明確にわからないからである。リスト1では、Jet4.0をプロバイダとして指定している。
    　ではJetのバージョンをいくつに指定するべきかは、どうすればわかるのだろうか。手っ取り早いのは、もうひとつの方法であるDataLinkファイルを作成してみて中身を参照することだ。つまり、はじめからDataLinkファイルを使用した方が効率的、というわけだ。
    　DataLinkファイルは、プロバイダなどの設定が保存されたファイルである。作成方法は単純だ。図6の〜にその手順を示しておく。

    図6：サンプルプログラムの作成手順
     
       
     
    エクスプローラを起動し、メニューから、［ファイル］-［新規作成］-［Microsoftデータリンク］を指定する。

       
     
    デフォルトのファイル名、「新規Microsoft Data Link.UDL」を適当な名前に変更する（ただし拡張子は変更しないように）。ここでは、例として「テスト.UDL」とする。

       
     
    「テスト.UDL」をダブルクリックし、データリンクプロパティダイアログを表示させる。プロバイダタブにて、使用するプロバイダを指定する。例えば、SQL Serverに接続したい場合は、「Microsoft OLE DB Provider for SQL Server」を選択すればいいし、本サンプルプログラムのようにAccessのデータベースに接続するには、「Microsoft Jet 4.0 OLE DB Provider」を選択する。選択後、［次へ］ボタンをクリックすると接続タブに移動する。

       
     
    接続タブでは、接続するための情報を入力する。Accessデータベースならば、単純にデータベースの場所を指定すればよい。指定後、［接続のテスト］ボタンをクリックし、「接続のテストが無事に完了しました。」とメッセージが出れば完了だ。
    ちなみに、接続させたい相手がSQL Serverである場合は、ここで、サーバー名とサーバーへのログオン設定、サーバー上のデータベース指定により、接続設定を行なう。

     
    データリンクプロパティダイアログボックスに戻り、［OK］ボタンをクリックすれば、DataLinkファイルは完成である。

    　では、作成したDataLinkファイルを用いてデータベースに接続するように、プログラムを修正してみよう。変更する場所は、リスト1のdbcn.Openの部分である。

     元のプログラム 

    ' OLE DBプロバイダを指定して、データソースを開く
' ここではプロバイダは、「Microsoft.Jet.OLEDB.4.0」
' データソースはAccess 2000のサンプルデータベースである
'「Northwind.mdb」を使用
dbcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;
　　　　　　DataSource=C:¥ProgramFiles¥MicrosoftOffice
　　　　　　¥Office¥Samples¥Northwind.mdb"

     修正したプログラム 

    ' OLE DBプロバイダを指定して、データソースを開く
dbcn.Open "File Name = C:¥テスト.UDL;"

    　随分スッキリすることが、おわかりいただけるだろう。またこの方法ならば、Jetがバージョンアップしたとしても、UDLファイルを変更するだけで、プログラムを書きかえる必要がないので非常に合理的だ。