● 動機と背景
最近、とある登録システムを作ることになり、都合でMicrosoft Accessを使うことになった。
Accessはパーソナルではあるが、確かにRDBしているので、そうそう簡単に操縦できるしろものではない。見た目が簡単なので、使ってみる人も多いのだが、その先が続かず、Excelとどこが違うの?
といった使い方に終わっていることも、多いのではないだろうか。
逆に、私のようにプログラミングを前提としてAccessを見た場合、ご親切機能はありがたいことはありがたいのだが、いまいち痒いところに手が届かないように思える。
そこで、Visual BASICをAccessのフロントエンドに、と思って書店に行ってみたら、目的にぴったりの本があった。
河野春夫。ADOを利用したVisual Basic 6.0 データベースプログラミングガイド。エーアイ出版株式会社、1999。ISBN 4-87193-688-0
詳しくは、この書籍を買っていただくとして、ここでは、この本を参考に当方で開発したプログラムの中から、役立ちそうな部分を紹介する。
● 前提となる基本ソフト構成
Access 97とVisual Basic 6.0を使用する。Access 2000/2002でも同様の方法で使える。
Accessはデータベースのテーブルの作成、マスタテーブル類のメンテナンスに使う。なにしろ、テーブルをお気楽に使うには最適のソフトで、私はもっと高度なデータベースのメンテナンス(ODBC経由)にも使っている。ちょっとした工夫で大いに役立ち、しかもOfficeに同梱のため経済的な気もする。
Visual Basic 6.0 (以下VB6と略記) は究極のVisual Basicで、Microsoft Officeを導入しているマシンでは、追加ソフトなしで動作する。文法はいかにも行き当たりばったりで当のMicrosoftですら制御不能に陥っているようで、今後はC#に移行するのかも知れない。しかし、プロが広範に使っており、Microsoftの主力商品の一つであるため、信頼感は抜群である。
この両者の仲を取り持つソフトも紆余曲折を経ており、今のところADOというのを使うのがよい、とされている。本ページでもADO
(ActiveX Data Objects)を使う。
● Visual Basicでの準備
Visual Basicのプロジェクトを新規作成したら、まず、ライブラリを追加する。
メニューの「プロジェクト」「参照設定」
Microsoft ActiveX Data Objects2.0 Library
にチェック
次に、プロジェクトにコンポーネントを追加する。
メニューの「プロジェクト」「コンポーネント」
Microsoft ADO Data Control 6.0 (OLEDB)
にチェック
これが ADODB である。オブジェクトクラスは Adodc、名は Adodc1など。とはいえ、このコントロールをフォームに貼り付けなくてもADOは使える。以下ではフォームに貼り付けない方法を使っている。
● 宣言と接続
Visual Basicの宣言部で、
Private cn As ADODB.Connection
Private rs As ADODB.Recordset
などと、ADOのコネクションとレコードセットを宣言しておく。レコードセットは複数必要な場合も考えられる。
接続はVisual Basicのアプリが動作している間は継続させるほうが良いだろう。Form
Loadサブルーチンで、
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
_
& " Data Source = C:\DB\Sample\hanbai.mdb"
cn.Open
DataとSourceの間の空白に注意。
コネクションはForm Unloadサブルーチンで明示的に切る。
cn.Close
Set cn = Nothing
一方、レコードセットは必要な都度に作成、消去する。
● テーブルの操作
▼ テーブル全体の参照
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
Do Until rs.EOF
x = rs!field1
y = rs!field2
...
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
▽ 値はNULL値のこともあるので、
If Not IsNull(rs!field1) Then
x = rs!field1
Else
x = ""
End If
などといったコーディングが必要となることがある。
table1やfield1のところには、実際のテーブル名やフィールド名を書く。rsは、いわゆるカーソルになっているので、Visual
Basicでは上述のように逐次処理する。
▼ SELECT / UPDATE / DELETE
Set rs = New ADODB.Recordset
sql1 = "SELECT文"
rs.Open sql1, cn, adOpenKeyset, adLockOptimistic, adCmdText
Do Until rs.EOF
x = rs!field1
y = rs!field2
...
( rs.Delete )
rs!field1 = x2
rs!field2 = y2
....
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
"SELECT文"のところは、ちゃんと動作するSQLのSELECT文を書く。つまりは、ODBCと同じく、SQLがインターフェースになっている。AccessにおけるSQLの注意は後述する。
▼ Insert
レコードの挿入は、以下の方法が楽だと思う。
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
rs.AddNew
rs!namae = "yoshio"
....
rs.Update
rs.Close
Set rs = Nothing
▼ DELETE
一括削除はSQLのDELETEコマンドが楽そう
cn.Execute "DELETE FROM table1 WHERE field1 = '" & x &
"'"
つまり、レコードセットではなく、コネクションのExecuteメソッドを使う。
▼ AccessのSQLについて
Microsoftの見解では、SQLには細かいところで各社に互換性がない、ということである。いずれにせよ、使いこなしが必要である。私の経験では、注意点は以下の通り。
文字列は ' で囲む。数字はそのままでよい。
日付は#yyyy/mm/dd#
列名 {NOT} LIKE 値指定 ワイルドカードは% 0字以上、_ 1字
2002年10月12日 岡田好一