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

» 2015年12月10日 05時00分 公開
[薬師寺国安PROJECT KySS]
前のページへ 1|2|3       

フォームがアクティブになったときの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)。


前のページへ 1|2|3       

Copyright © ITmedia, Inc. All Rights Reserved.

スポンサーからのお知らせPR

注目のテーマ

AI for エンジニアリング
「サプライチェーン攻撃」対策
1P情シスのための脆弱性管理/対策の現実解
OSSのサプライチェーン管理、取るべきアクションとは
Microsoft & Windows最前線2024
システム開発ノウハウ 【発注ナビ】PR
あなたにおすすめの記事PR

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。