パソコン活用研究5番街（Visual Basic、Excel(VBA)、BASIC　プログラミング研究）

ADOによるデータベース（MySQL)制御

EXCELをユーザーインタフェース（フロントエンド）のアプリケーションとして使用し、データベース
を制御する方法について研究してみます。

データベースとしてははフリーで個人で使うにも使いやすいMySQLをとりあげます。

１　データベースの準備
まずは、MySQLで以下のようなデータベースを準備してください。
データベース名　test
テーブル名　　　　programing
テーブルの構造およびデータの内容は以下の通りです。

+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| article  | int(3) unsigned zerofill |      | PRI | 000     |       |
| language | char(15)                 |      |     |         |       |
+----------+--------------------------+------+-----+---------+-------+

+---------+----------+
| article | language |
+---------+----------+
| 001     | APL      |
| 002     | BASIC    |
| 003     | C        |
| 004     | DELFI    |
| 005     | FORTRAN  |
| 006     | COBOL    |
+---------+----------+

データベースの内容については、「インターネット＆ＰＣ120%活用/mySQLのインストール」
を参照して下さい。


２　データソース
ADOでデータベースにアクセスするためには、ODBCのデータソースを作成しておく必要が
あります。MySQLではMyODBCを使います。
MyODBCをインストールすると、MyODBCフォルダにadmindemo.exeがあります。
これを起動すると、「ODBC Admin Demo」が立ち上がりますので、メニュバー「Info」の
「Manage Data Sources」を選択して下さい。
以下のように「ODBC　データソースアドミニストレーター」の画面が立ち上がりますので
「システムDSN」タブで「追加」をクリックして下さい。
図１

「データソース新規作成」の画面が以下のように立ち上がりますので、「MySQL」を選択し
「完了」ボタンをクリックします。

最後に、「TDX MySQL Driver default Configuration」に必要項目の記入をして完了です。

Windows DSN Name　アプリケーションから見える名前を記入します。
MySQL Host　　　　　データベースのあるホスト名（または、ＩＰアドレス）。同じＰＣ上の場合はlocalhost。
MySQL Database name　データベース名
Port　　　　　　　　　　デフォルトでは3306

これで、準備完了です。


＜Windows XPの場合＞
Windows XP（NT,2000も同様かと思われますが）にMyODBCをインストールするとMyODBCフォルダ
は作成されません。ODBCデータソースアドミニストレーターは、「スタート」−「コントロールパネル」
−「管理ツール」−「データソース(ODBC)」で起動して下さい。
図１のODBCデータソースアドミニストレーターが起動します。


３　EXCELでデータベース制御
（１）　ADOの参照設定
EXCELのVBAからADOを扱えるようにするために、ADOライブラリーをシステムに組み込みます。
Visual Basic Editor のウィンドウで、「ツール」−「参照設定」を選択し、「参照設定」ダイアログを
開きます。
そして、以下の通りMicrosoft ActiveX data Object Library にチェックを入れて、「OK」ボタンを
クリックします。

これで、ADOによりMySQLに接続する準備完了です。

２）　ADOによるデータベース（MySQL)接続

ここでは、一番簡単な例として、上で作成したデータベースtestのテーブルprogramingのデータを
Excelのシートに取り込んでみます。
ADOによるデータベースへの接続のためのVBAコードはいくつか記述の方法があります。
順をおってみてみましょう。

（A） Recordsetオブジェクトを使って接続する

普段あまりみなれないコードだと思います。詳細は別の機会に説明するとして、ここでは簡単に
必要事項だけ説明します。
まず、ADOオブジェクトについて簡単に触れておきましょう。
ADODB　ADOオブジェクトのTopオブジェクトです。
Recordset　データの移動、操作を行うためのオブジェクトです。その下にFieldオブジェクトや
　　　　　　　　Open,Movefirst（レコードセットの最初に移動）,Movenext（次に移動）　といった
　　　　　　　メソッドがあります。
Field　　レコードセットの列を表すオブジェクトです。
Fields　Fieldのコレクションオブジェクトです。

コードについて簡単に説明します。
�@　変数myrecをRecordsetオブジェクトとして宣言します
�A　新しいRecordsetオブジェクト（myrec）を生成します。　
�B　変数cntの文字列は、Excelを外部のデータベースに接続するための接続文字列で、データベース
　　　毎に、接続文字列は固有のものが決まっています。ここではMySQLへの接続文字列です。
　　　基本書式は以下の様です。
　　　Provider = プロバイダ名　;Data Source =　データソースのパス
　　　＊データソースは、上の「２　データソース」で作成したデータソースです。
�C　SQL文です。テーブル「programing」から全てのフィールドデータを検索します。
�D　データベースをオープンし、操作可能にします
�E　レコードセットの最初の位置を参照します。
�F　while ............ loop で各フィールドデータ値をセルに代入します。
�G　データベースへの接続をクローズします。
�H　オブジェクトへの参照を破棄します。

このVBAの実行結果は以下の通りです。



（B） CreateObjectを使う

�@　ADODB.Connectionオブジェクトを生成します。
�A　変数rsは、"select * from programing"というSQL文により生成されるRecordsetになります。
以下は、（A）と同じです。


（C）　udlファイルを使って接続する例

�@　データリンクファイルを使ってデータベースに接続します。

データリンクファイル（ .udl ）は、プロバイダやデータソースの設定などを保存したテキストファイルです。
データリンクファイルは以下の手順で作成します。
Exploreなどで、「ファイル」−＞「新規作成」−＞「Microsoftデータリンク」とメニューをたどり
新規Microsoftデータリンク.udl というファイルを作ります。このファイルを適当な名前に変更して
保存して下さい。このファイルを（ダブル）クリックすると、データリンクプロパティというダイアログボックス
が開きます。
「プロバイダ」タブでは、MySQLを選択して下さい。
「接続」タブでは、以下のような項目を設定します。
一番単純には、「TDX MySQL Driver default Configuration」で記入したWindows DNS Name を
「１．データソースを指定します。」のデータソース名の欄に記入すれば、とりあえずＯ．Ｋ．です。　
以下必要なら必要項目の記入をして完了です。

（D）　Connectionオブジェクトを使う
（C）とほぼ同じですが、データベースをオープンするのに、接続文字列を使った例です。
一番オーソドックス？かもしれません。