教えて!ExcelVBA!

ExcelVBAの基礎知識・書き方について紹介します。

【ExcelVBA 外部アプリケーション連携】(Access連携)Accessテーブルデータを削除するにはどうすればいいの?教えて!

f:id:m_kbou:20210628095953p:plain

Accessテーブルデータを削除する方法について紹介します。ExcelとAccessとの連携によるデータのやり取りでは基本中の基本操作となります。是非一連の流れを理解して活用してみて下さい。

 

 

構文

記述方法は以下の通りとなります。

①データベースへの接続
Set[変数1]= OpenDatabase(”[データベースの登録パス]”)

②テーブルへの接続
Set[変数2]=[変数1].OpenRecordset(”[SQL文]”)

③テーブルデータの検索
[変数2].FindFirst "[変数2の項目名] = '[検索条件]'"
If (Not [変数2].NoMatch) Then
  -----------------------------
  [変数2].Delete
  -----------------------------
End If

②テーブルの切断
[変数2].Close
Set[変数2]= Nothing

①データベースの切断
[変数1].Close
Set[変数1]= Nothing 

[説明]:

テーブルデータを更新するには

-------------------------------
[変数2].Delete
-------------------------------

と記述します。
記述についての説明は以下の通りです。

・[変数2].Delete
 ⇒「対象データをテーブル([変数2])より削除(Delete)して下さい。」との意味になります。

-------------------------------

※Accessテーブルのデータを操作するには、事前にAccessデータベースへの接続(①データベースへの接続)やテーブルへの接続(②テーブルへの接続)が必要となります。また、接続した後はテーブルの切断(②テーブルの切断)やデータベースの切断(①データベースの切断)も必要となります。データベースへの接続/切断についてはこちらを、テーブルへの接続/切断についてはこちらを参照して下さい。

-------------------------------

※Accessテーブルデータを更新するには、更新データを検索条件で検索(③テーブルデータの検索)し、見つかった場合には更新データをセットしてから更新処理を行います。更新データを検索する方法についてはこちらを参照して下さい。

-------------------------------

[記述例]:

r.Delete
 

使い方

使い方について具体的に説明します。

(例①)テーブル中の1件の対象データを削除する場合

テーブル中の1件の対象データを削除する場合について説明します。

[プログラミング例]:

Sub サンプル()

  '↓処理①
  Dim db As Database
  Dim r As Recordset

  '↓処理②
  Set db = OpenDatabase("C:\テスト\従業員.accdb")

  '↓処理③
  Set r = db.OpenRecordset("select * from T_従業員")

  '↓処理④
  r.FindFirst "番号 = '" & Trim(Range("A2")) & "'"
  If (Not r.NoMatch) Then
    r.Delete
    MsgBox "削除しました。"
  Else
    MsgBox "指定した番号:" & Trim(Range("A2")) & "が存在しません。", 16, "メッセージ"
  End If

  '↓処理⑤
  r.Close
  Set r = Nothing

  '↓処理⑥
  db.Close
  Set db = Nothing

End Sub

処理の流れは以下の通りとなります。

[処理①]:変数定義
データベース/テーブルへの接続他を格納するための変数定義となります。

[処理②]:データベースへの接続
※データベースへの接続についてはこちらを参照して下さい。

[処理③]:テーブルへの接続
※テーブルへの接続についてはこちらを参照して下さい。

[処理④]:テーブル中の対象データを削除
A2セルに入力されている番号でテーブルの項目:番号を検索し、IF文を使用して検索結果が一致したか否かを判定します。一致した場合には、対象データをテーブルから削除します。また、”削除しました。”をMsgBoxで表示します。一致しなかった場合には、検索した番号が存在しないメッセージを表示します。

[処理⑤]:テーブルの切断
※テーブルの切断についてはこちらを参照して下さい。

[処理⑥]:データベースの切断
※データベースの切断についてはこちらを参照して下さい。

f:id:m_kbou:20210628095938p:plain

※上記のプログラミング例は、VBE(VBA記述画面)に記述しないと実行ができません。VBEの開き方についてはこちらを参考にして下さい。また、Accessデータベースとの接続には事前設定が必要となります。事前準備はこちらを参考にして下さい。

[実行例]:

(事前準備)

C:\テストにAccessデータベース(従業員.accdb)が登録されている必要があります。

f:id:m_kbou:20200511165951p:plain

また、従業員.accdbデータベースの中にはT_従業員テーブルが登録されています。

f:id:m_kbou:20210625102649p:plain

更に、T_従業員テーブルの中には以下4件のデータがセットされています。
(1件目)氏名:大山、番号:0001、部署:総務部、性別:男、年齢:50
(2件目)氏名:久保木、番号:0002、部署:経理部、性別:男、年齢:45
(3件目)氏名:遠藤、番号:0003、部署:資材部、性別:女、年齢:35
(4件目)氏名:岡田、番号:0004、部署:総務部、性別:女、年齢:25

f:id:m_kbou:20210628100031p:plain

(実行内容)

①A2セル~E2セルに更新データが登録されています。また、<実行>ボタンには上記のプログラミング例のプログラムが登録されています。この<実行>ボタンをクリックします。(※ボタンの作り方やボタンにプログラムを割り当てるにはこちらを参考にして下さい。)

f:id:m_kbou:20210628100046p:plain

②「削除しました。」がMsgBoxで表示されます。

f:id:m_kbou:20210628100100p:plain

③再度T_従業員テーブルの中を確認すると、
1件目のデータ
 氏名:大山、番号:0001、部署:総務部、性別:男、年齢:50
がテーブルから削除された事が分かります。
(※A2セルに入力されていた番号:0001でT_従業員テーブルの項目:番号を検索した結果、テーブル中の1件目のデータに一致したため、1件目のデータを削除しました。)

f:id:m_kbou:20210628100113p:plain

[サンプル]:

上記で説明したファイルをダウンロードできます。ご自由にお使い下さい。

drive.google.com

Accessテーブルデータを削除する方法(1件を削除する場合)についての説明は以上です。

 

(例②)テーブル中の複数件の対象データを削除する場合

テーブル中の複数件の対象データを削除する場合について説明します。

[プログラミング例]:

Sub サンプル()

  '↓処理①
  Dim db As Database
  Dim r As Recordset
  Dim 行 As Integer

  '↓処理②
  Set db = OpenDatabase("C:\テスト\従業員.accdb")

  '↓処理③
  Set r = db.OpenRecordset("select * from T_従業員")

  '↓処理④
  For 行 = 2 To 4
    r.FindFirst "番号 = '" & Trim(Range("A" & 行)) & "'"
    If (Not r.NoMatch) Then
      r.Delete
    Else
      MsgBox "指定した番号:" & Trim(Range("A" & 行)) & "が存在しません。", 16, "メッセージ"
      GoTo 終了処理
    End If
  Next
  MsgBox "削除しました。"

終了処理:

  '↓処理⑤
  r.Close
  Set r = Nothing

  '↓処理⑥
  db.Close
  Set db = Nothing

End Sub

処理の流れは以下の通りとなります。

[処理①]:変数定義
データベース/テーブルへの接続他を格納するための変数定義となります。

[処理②]:データベースへの接続
※データベースへの接続についてはこちらを参照して下さい。

[処理③]:テーブルへの接続
※テーブルへの接続についてはこちらを参照して下さい。

[処理④]:テーブル中の対象データを削除
複数件のデータをテーブルから削除するために、For~Next文を使用して処理を繰り返し実行します。繰り返し処理する内容は「A列に入力されている番号でテーブルの項目:番号を検索し、IF文を使用して検索結果が一致したか否かを判定します。一致した場合には、テーブルから対象データを削除します。また、一致しなかった場合には、検索した番号が存在しないメッセージを表示し、処理を途中で中断します。」となります。この処理は2行目~4行目まで繰り返し行い、途中で中断されなかった場合には、最後に”削除しました。”をMsgBoxで表示します。

[処理⑤]:テーブルの切断
※テーブルの切断についてはこちらを参照して下さい。

[処理⑥]:データベースの切断
※データベースの切断についてはこちらを参照して下さい。

f:id:m_kbou:20210628101954p:plain

※上記のプログラミング例は、VBE(VBA記述画面)に記述しないと実行ができません。VBEの開き方についてはこちらを参考にして下さい。また、Accessデータベースとの接続には事前設定が必要となります。事前準備はこちらを参考にして下さい。

[実行例]:

(事前準備)

C:\テストにAccessデータベース(従業員.accdb)が登録されている必要があります。

f:id:m_kbou:20200511165951p:plain

また、従業員.accdbデータベースの中にはT_従業員テーブルが登録されています。

f:id:m_kbou:20210625102649p:plain

更に、T_従業員テーブルの中には以下4件のデータがセットされています。
(1件目)氏名:大山、番号:0001、部署:総務部、性別:男、年齢:50
(2件目)氏名:久保木、番号:0002、部署:経理部、性別:男、年齢:45
(3件目)氏名:遠藤、番号:0003、部署:資材部、性別:女、年齢:35
(4件目)氏名:岡田、番号:0004、部署:総務部、性別:女、年齢:25

f:id:m_kbou:20210628102016p:plain

(実行内容)

①A2セル~E4セルにデータが登録されています。また、<実行>ボタンには上記のプログラミング例のプログラムが登録されています。この<実行>ボタンをクリックします。(※ボタンの作り方やボタンにプログラムを割り当てるにはこちらを参考にして下さい。)

f:id:m_kbou:20210628102034p:plain

②「削除しました。」がMsgBoxで表示されます。

f:id:m_kbou:20210628102050p:plain

③再度T_従業員テーブルの中を確認すると、登録されているデータは
 氏名:久保木、番号:0002、部署:経理部、性別:男、年齢:45
の1件のみとなります。
(※A列に入力された番号でT_従業員テーブルの項目:番号を検索した結果、テーブルの1件目・3件目・4件目のデータと一致したため、データが削除されました。)

f:id:m_kbou:20210628102104p:plain

[サンプル]:

上記で説明したファイルをダウンロードできます。ご自由にお使い下さい。

drive.google.com

Accessテーブルデータを削除する方法(複数件を削除する場合)についての説明は以上です。

 

おわりに

今回はAccessテーブルデータを削除する方法について説明しました。類似の内容にテーブルデータの更新する方法もあります。こちらも是非参考にして下さい。また、今回の様にExcel⇔Accessの連携した処理を行う場合に関しては、予め事前の準備作業が必要となります。こちらも忘れずに実施しておくようにお願いします。