Excelを入力画面にしてAccessデータベースを参照・更新・削除する際の便利テクニック：VBA／マクロ便利Tips（3/3 ページ）

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、RecordsetのOpenメソッドでSQL文を使う方法や、MoveNext、Find、Deleteメソッド、EOFプロパティの使い方、Recordsetの各フィールドの参照方法などについて。

[薬師寺国安，PROJECT KySS]

フォームがアクティブになったときのVBAコード

　「プロジェクト」内の「UserForm1」を選択し、マウスの右クリックで表示されるメニューから「コード」の表示を選択する。コードエディター画面が表示されるので、まずは、フォームがアクティブになったときの処理を記述する。フォームがアクティブになったときのプロシージャを作成するには、図9の手順に従う。

図9　プロシージャのコードを作成する方法

　これに、リスト3のコードを記述する。

Private Sub UserForm_Activate()
  Dim myCon As New ADODB.Connection
  Dim myRecordSet As New ADODB.Recordset
  Dim mySQL As String
  Dim dbFile As Variant
    
  dbFile = "K:\Access_Excel VBA Tips\商品管理データベース.accdb"
  myCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbFile & ""
  myCon.Open
  
  mySQL = "SELECT 商品NO FROM 商品管理テーブル"
  
  myRecordSet.Open mySQL, myCon, adOpenDynamic
  
  Do Until myRecordSet.EOF
    商品NOリストボックス.AddItem (myRecordSet!商品NO)
    myRecordSet.MoveNext
  Loop
  
  myRecordSet.Close
  Set myRecordSet = Nothing
  myCon.Close
  Set myCon = Nothing
End Sub

リスト3　フォームがアクティブになった時「リストボックス」に「商品NO」の一覧を表示するコード

　処理の詳細については、リスト1やリスト2の説明通りなので、省略する。これで、フォームがアクティブになったときに「リストボックス」内に「商品NO」の一覧が表示される。

「削除ボタン」がクリックされたときのVBAコード

　次に、図9の方法で、「削除ボタン」がクリックされたときのプロシージャを作成し、リスト4のコードを記述する。

Private Sub 削除ボタン_Click()
  Dim myCon As New ADODB.Connection
  Dim myRecordSet As New ADODB.Recordset
  Dim dbFile As Variant
  
  dbFile = "K:\Access_Excel VBA Tips\商品管理データベース.accdb"
  myCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbFile & ""
  myCon.Open
   
  myRecordSet.Open Source:="商品管理テーブル", ActiveConnection:=myCon, CursorType:=adOpenKeyset, LockType:=adLockOptimistic
  
  With myRecordSet
    .Find Criteria:="商品NO='" &商品NOリストボックス.Text & "'"
    If .EOF = True Then
      MsgBox ("該当レコードはありません。")
      Exit Sub
    End If
    .Delete
  End With
  
  MsgBox (商品NOリストボックス.Text & "のレコードを削除しました。")
  
  myRecordSet.Close
  Set myRecordSet = Nothing
  myCon.Close
  Set myCon = Nothing
  
  ListBox1.Clear
  Call UserForm_Activate
End Sub

リスト4　「削除ボタン」がクリックされたときのコード

　最初の方は、リスト1とほとんど同じなので、解説は省略する。以下、コードについては、リスト1〜3と異なる部分のみ解説する。

指定した条件と一致するレコードを検索する「ADODB.Recordset.Find」メソッド

　指定した条件と一致するレコードを検索するには、下記の書式を使用する。

ADODB.Recordset.Findメソッドの書式

Recordset.Find Criteria:=｛検索条件｝

　｛検索条件｝には今回は、「商品NO」が「商品NOリストボックス」から選択された値に合致するレコードを指定している（13行目）。

レコードを削除する「ADODB.Recordset.Delete」メソッド

　レコードの最後まで検索して、合致する条件のレコードがなかった場合は、警告メッセージを表示して処理を抜ける。該当するレコードがあった場合は、Deleteメソッドで削除し、削除した旨のメッセージを表示する（18行目）。

フォームを開く処理を追加して、実行

　「商品NOリストボックス」内を一度クリアし（28行目）、再度削除された「商品管理テーブル」から「商品NO」を読み込むために、UserForm1_Activateプロシージャを再実行する（29行目）。

　このUserForm1を開くためのコードを記述する。このコードはModule内に記述する必要がある。プロジェクトからModule1を選択して、リスト5のコードを記述する。

Sub レコード削除フォーム()
 UserForm1.Show
End Sub

リスト5　レコード削除フォームを表示するコード

　図5の「商品NO読み込みフォーム」ボタンに、リスト5の「レコード削除フォーム」を関連付けて実行してみよう。図10のようになる。

図10　「リストボックス」から選択した「商品NO」のレコードが削除された

次回からはWordとExcelを連携するVBAテクニック

　今回はこれで終わりだ。Excelのシート上にAccessの商品管理テーブルのデータを読み込み、Excel上で編集して、再度Accessに書き戻す。わざわざ、Accessを起動することなく、Excelだけで、ExcelとAccessのデータが操作できるのは、実際の業務において大変に便利だと思う。

　また、Excelでフォームを使用すると、シート上で「商品管理テーブル」を処理するよりも、もっと容易に、柔軟性を持ってデータの処理が実現できるのも便利ではないだろうか。

　今まで解説してきたTipsを組み合わせると、十分に日常の業務で利用できるAccess＋Excelのシステムが作成できるのではないだろうか。これらのサンプルを各自がアレンジして、ぜひ日常の業務で利用していただきたい。

　次回からは、WordとExcelを連携するVBAテクニックを解説するので、お楽しみに。

著者紹介

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms - Windows Platform Development (Oct 2014-Sep 2015）。