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

2018年08月21日 · 2 分で読了 · Access VBA

概要

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