【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のデータを更新するには参照設定を行う必要があります。
参照設定の方法は以下をご覧ください。
https://kazusa-pg.com/vba-references/
参照設定を起動してMicrosoft ActiveX Data Objects 6.1 Libraryにチェックを入れます。

データを更新するコードは以下になります。
1Option Explicit
2Sub updatePrice()
3
4 Dim priceUpdateSht As Worksheet
5 Set priceUpdateSht = ThisWorkbook.Worksheets("priceUpdate")
6
7 Dim adoCON As New ADODB.Connection
8 adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & _
9 "Data Source=" & "C:\access-update-test\MenuData.accdb" & ";"
10 adoCON.Open
11
12 Dim id As Long
13 Dim price As Long
14 Dim personName As String
15 id = priceUpdateSht.Range("A3")
16 price = priceUpdateSht.Range("B3")
17 personName = priceUpdateSht.Range("C3")
18
19 Dim adoRS As New ADODB.Recordset
20 adoRS.Open "SELECT * FROM T_メニュー WHERE ID = " & id, adoCON, adOpenDynamic, adLockPessimistic
21 adoRS!値段 = price
22 adoRS!データ更新者 = personName
23 adoRS!データ更新日 = Now()
24 adoRS.Update
25 adoRS.Close
26
27 adoCON.Close
28
29 Set priceUpdateSht = Nothing
30 Set adoCON = Nothing
31 Set adoRS = Nothing
32
33 MsgBox "メニューの値段を更新しました", vbInformation, "更新完了"
34
35End Sub
データの更新テスト
更新ボタンを押した時にupdatePriceプロシージャを呼び出すように設定します。


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

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

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