プログラミングへ戻る

Visual BASICでAccessファイルを使う

● 動機と背景

 最近、とある登録システムを作ることになり、都合で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日 岡田好一