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

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

[薬師寺国安，PROJECT KySS] PC用表示関連情報

LINE

Hatena

前のページへ | 次のページへ

「編集保存」ボタンがクリックされたときの処理

　次に、図2の「編集保存」ボタンがクリックされたときの処理を記述する。同じ「Module1」内にリスト2のコードを記述する。

Sub データを編集してAccessへ出力()
  Dim myCon As New ADODB.Connection
  Dim myRecordSet As New ADODB.Recordset
  Dim mySQL As String
  Dim dbFile As Variant
  Dim i As Long
  dbFile = "K:\Access_Excel VBA Tips\商品管理データベース.accdb"
  myCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbFile & ""
  myCon.Open
  
  myRecordSet.Open "DELETE * FROM 商品管理テーブル;", myCon
  
  mySQL = "SELECT * FROM 商品管理テーブル"
  
  myRecordSet.Open mySQL, myCon, adOpenDynamic, adLockOptimistic
  
  i = 2
  
  Do While (Cells(i, 1).Value <> "")
   myRecordSet.AddNew
    myRecordSet.Fields("商品NO") = Cells(i, 1).Value
    myRecordSet.Fields("商品名") = Cells(i, 2).Value
    myRecordSet.Fields("単価") = Cells(i, 3).Value
    myRecordSet.Fields("在庫") = Cells(i, 4).Value
    myRecordSet.Update
    i = i + 1
  Loop
  
  myRecordSet.Close
  myCon.Close
  
  Set myRecordSet = Nothing
  Set myCon = Nothing
  
  MsgBox ("データを更新しました。")
    
End Sub

リスト2　読み込まれた「商品管理テーブル」のデータを編集して、「編集保存」ボタンをクリックしたときのコード

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

ADODB.Recordset.OpenでDELETE文のSQLを指定

　11行目で、一度「商品管理テーブル」のレコードを、DELETE文のSQLを使うことで全削除（初期化）しておく。この処理を追加しておかないと、編集される前のデータの下に、編集後のデータ（レコード）が追加されてしまうのだ。それで、一度全てのレコードを削除しておく必要がある。

　このように、ADODB.Recordset.Openメソッドは、SELECT文で参照処理に使えるだけではなくDELETE文の削除処理のように、他にも使えることを覚えておこう。

「データを編集してAccessへ出力」の実行

　リスト1の「Accessの商品管理データを読み込む」を図2の「Accessの商品管理データを読み込む」ボタンと関連付け、リスト2の「データを編集してAccessへ出力」を図2の「編集保存」と関連付けて、実行すると図4のようになる。

図4　「Windows Phone 8.1」の「単価」を「9800」、「在庫」を「2」に変更してみた

ExcelからAccessのデータを削除する

　次の処理は、Excelの入力画面にフォームを使う。

　「商品NOの読み込みフォーム」という「ボタン」をシート上に配置し、「ボタン」をクリックすると、「リストボックス」内に「商品NO」の表示されたフォームが現れる。「リストボックス」内の「商品NO」を選択して、「この商品を削除」ボタンをクリックすると、該当する「商品NO」のレコードが、Accessの「商品管理テーブル」から削除されるというものだ。

Excelで入力フォームを作成

　まずは、「商品管理データベース.xlsm」をクリックして、Excelを起動する。シートの下に表示されている「○に＋」のアイコンをクリックして、「Sheet1」を追加し、名前を「商品の削除」としておく。

　「商品の削除」のシートに、Accessの「商品管理テーブル」から、「商品NO」を表示させるフォームを表示する、「商品NO読み込みフォーム」ボタンを配置する（図5）。ボタンは、いつもの通り「図形」の「角丸四角形」を使用する。

図5　「商品の削除」のシートに「商品NO読み込みフォーム」ボタンを配置する

　Excelメニューの「開発」→「Visual Basic」と選択して、「VBE」を起動する。

　「VBE」のメニューから「挿入」→「ユーザフォーム」を選択する。すると「プロジェクト」内に「UserForm1」が追加され、「UserForm1」のデザイン画面が表示される。「UserForm1」のサイズを少し大きくしておく（図6）。

図6　「UserForm1」のデザイン画面が表示された

　「UserForm1」にフォーカスを移すと、「ツールボックス」が表示されるので、「リストボックス」を選択して配置する。また、「ボタン」も配置しておく。項目名となる「ラベル」も配置しておく（図7）。

図7　「ツールボックス」から「UserForm1」上に「ラベル」、「リストボックス」と「ボタン」を配置した

　「ラベル」を選択し、プロパティから「Caption」に「商品NO」と指定する。「Font」を選択し、表示される画面から、「フォント名」に「Meiryo UI」、「スタイル」に「太字」、「サイズ」に「14」と選択しておく。次に、プロパティの「TextAlign」に「fmTextAlignCenter」を選択して「中央揃え」にしておく。

　次に「リストボックス」を選択し、プロパティから「オブジェク名」に「商品NOリストボックス」と指定する。「Font」を選択し、表示される画面から、「フォント名」に「Meiryo UI」、「スタイル」に「太字」、「サイズ」に「14」と選択しておく。

　次に「ボタン」を選択し、「オブジェクト名」に「削除ボタン」と指定し、「Caption」に「この商品を削除」と指定する。「Font」に関しては、「ラベル」や「リストボックス」と同じ書式を指定する。

　最後に「フォーム」を選択し、「Caption」に「商品削除フォーム」と指定する。

　全て設定すると図8のようになる。

図8　各コントロールの書式を設定した

Recordset.Findメソッド、Recordset.Deleteメソッド