【VBA】ExcelからADOでAccessのデータを更新する

ExcelからVBAでAccessのデータ更新してみます。

今回扱うAccessファイルの概要

Accessファイルは以下のパスに置かれています。
C:\access-update-test\MenuData.accdb

Accessにはテーブル「T_メニュー」があり、T_メニューにはメニューデータが記録されています。

テーブルは以下のフィールドで構成されています。

フィールド名	データ型
ID	オートナンバー型
メニュー名	短いテキスト
値段	数値型
データ新規登録者	短いテキスト
データ新規登録日	日付/時刻型
データ更新者	短いテキスト
データ更新日	日付/時刻型

更新用フォーム

IDを指定してデータを更新したいので、データ更新用のフォームをシートに用意します。
シート名はPriceUpdateとします。
ID、値段、データ更新者の名前を入力して更新ボタンを押すとデータを更新します。

今回はCさんがカレーの値段を780円から880円に更新すると仮定します。

A3セルに更新したいメニューのIDを指定します。カレーのIDの1を入力します。
B3セルに更新したい値段を入力します。880を入力します。
C3セルにデータ更新者の名前を入力します。
今回はCさんが更新するので”Cさん”と入力します。

Accessデータ更新のVBAコード

参照設定

ExcelからADOを利用してAccessのデータを更新するには参照設定を行う必要があります。
参照設定の方法は以下をご覧ください。

VBAで参照設定を行う

参照設定を起動してMicrosoft ActiveX Data Objects 6.1 Libraryにチェックを入れます。

データを更新するコードは以下になります。

Option Explicit
Sub updatePrice()
  
  Dim priceUpdateSht As Worksheet
  Set priceUpdateSht = ThisWorkbook.Worksheets("priceUpdate")
  
  Dim adoCON As New ADODB.Connection
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & "C:\access-update-test\MenuData.accdb" & ";"
  adoCON.Open
  
  Dim id As Long
  Dim price As Long
  Dim personName As String
  id = priceUpdateSht.Range("A3")
  price = priceUpdateSht.Range("B3")
  personName = priceUpdateSht.Range("C3")
  
  Dim adoRS  As New ADODB.Recordset
  adoRS.Open "SELECT * FROM T_メニュー WHERE ID = " & id, adoCON, adOpenDynamic, adLockPessimistic
  adoRS!値段 = price
  adoRS!データ更新者 = personName
  adoRS!データ更新日 = Now()
  adoRS.Update
  adoRS.Close

  adoCON.Close
  
  Set priceUpdateSht = Nothing
  Set adoCON = Nothing
  Set adoRS = Nothing
  
  MsgBox "メニューの値段を更新しました", vbInformation, "更新完了"
  
End Sub

データの更新テスト

更新ボタンを押した時にupdatePriceプロシージャを呼び出すように設定します。

更新ボタンをクリックするとデータを更新します。

更新が完了するとメッセージボックスが表示されます。

Accessのデータを見てみると、カレーの値段・データ更新日・データ更新者が更新されています。