教えて!ExcelVBA!

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

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

f:id:m_kbou:20210627065817p:plain

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

 

 

構文

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

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

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

③テーブルデータの検索
[変数2].FindFirst "[変数2の項目名] = '[検索条件]'"
If (Not [変数2].NoMatch) Then
  -----------------------------
  [変数2].Edit
  [変数2]![項目]=[シート名及びセル]
  [変数2].Update
  -----------------------------
End If

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

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

[説明]:

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

-------------------------------
[変数2].Edit
[変数2]![項目]=[シート名及びセル]
[変数2].Update
-------------------------------

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

・[変数2].Edit
 ⇒「この後の処理に書かれている内容でテーブル([変数2])にデータをセットして更新(Edit)して下さい。」との意味になります。

・[変数2]![項目]=[シート名及びセル]
 ⇒Excelシートのセル([シート名及びセル])に入力されているデータを、テーブル([変数2])の項目([項目])にセットして下さい。」との意味になります。

・[変数2].Update
 ⇒「上記でテーブル([変数2])に追加したデータを保存(Update)して下さい。」との意味になります。

内容を纏めると、「指定したシートのセル([シート名及びセル])に入力されているデータを、テーブル([変数2])の項目([項目])にセット更新して下さい。」との意味になります。

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

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

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

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

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

[記述例]:

r.Edit
r![部署] = Sheets("従業員").Range("C1")
r.Update

 

使い方

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

(例①)テーブルへ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.Edit
    r![部署] = Sheets("Sheet1").Range("C2")
    r![性別] = Sheets("Sheet1").Range("D2")
    r![年齢] = Sheets("Sheet1").Range("E2")
    r.Update
    MsgBox "更新しました。"
  Else
    MsgBox "指定した番号:" & Trim(Range("A2")) & "が存在しません。", 16, "メッセージ"
  End If

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

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

End Sub

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

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

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

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

[処理④]:テーブルへ値をセット更新
A2セルに入力されている番号でテーブルの項目:番号を検索し、IF文を使用して検索結果が一致したか否かを判定します。一致した場合には、Editでテーブルへの更新宣言を行った後にC2セル~E2セルに入力されている値をテーブルの各項目(部署・性別・年齢)にセットし、最後にUpdateで保存します。また、”更新しました。”をMsgBoxで表示します。一致しなかった場合には、検索した番号が存在しないメッセージを表示します。

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

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

f:id:m_kbou:20210628092847p: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
となっています。

f:id:m_kbou:20210627065920p:plain

(実行内容)

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

f:id:m_kbou:20210627065936p:plain

②「更新しました。」がMsgBoxで表示されます。

f:id:m_kbou:20210627065950p:plain

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

f:id:m_kbou:20210627070004p: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.Edit
      r![部署] = Sheets("Sheet1").Range("C" & 行)
      r![性別] = Sheets("Sheet1").Range("D" & 行)
      r![年齢] = Sheets("Sheet1").Range("E" & 行)
      r.Update
    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文を使用して検索結果が一致したか否かを判定します。一致した場合には、Editでテーブルへの更新宣言を行った後にC列~E列に入力されている値をテーブルの各項目(部署・性別・年齢)にセットし、最後にUpdateで保存します。また、一致しなかった場合には、検索した番号が存在しないメッセージを表示し、処理を途中で中断します。」となります。この処理は2行目~4行目まで繰り返し行い、途中で中断されなかった場合には、最後に”更新しました。”をMsgBoxで表示します。

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

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

f:id:m_kbou:20210628084947p: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:20210627073506p:plain

(実行内容)

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

f:id:m_kbou:20210627073521p:plain

②「更新しました。」がMsgBoxで表示されます。

f:id:m_kbou:20210627073535p:plain

③再度T_従業員テーブルの中を確認すると、

-------------------------------
1件目のデータが
 氏名:大山、番号:0001、部署:経理部、性別:女、年齢:25
となり、
 部署が「総務部」→「経理部」、性別が「男」→「女」、年齢が「50」→「25」
に変更された事が分かります。

-------------------------------
3件目のデータが
 氏名:遠藤、番号:0003、部署:経理部、性別:女、年齢:35
となり、
 部署が「資材部」→「経理部」
に変更された事が分かります。

-------------------------------
4件目のデータが
 氏名:岡田、番号:0004、部署:退職、性別:-、年齢:25
となり、
 部署が「総務部」→「退職」、性別が「女」→「-」
に変更された事が分かります。

-------------------------------
(※A列に入力された番号でT_従業員テーブルの項目:番号を検索した結果、テーブルの1件目・3件目・4件目のデータと一致したため、部署・性別・年齢等の値が更新されました。)

f:id:m_kbou:20210627073549p:plain

[サンプル]:

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

drive.google.com

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

 

おわりに

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