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

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

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

#

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

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

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

更新用フォーム

#

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

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

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

Accessデータ更新のVBAコード

#

参照設定

#

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

参照設定を起動して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のデータを見てみると、カレーの値段・データ更新日・データ更新者が更新されています。