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で参照設定を行う
VBAに標準で含まれていない機能があるときは
外部のライブラリを参照して、外部ライブラリ内の機能を利用します。
この外部ライブラリの機能を利用する設定を、参照設定と言います。
kazusa-pg.com
2018.07.13
参照設定を起動してMicrosoft ActiveX Data Objects 6.1 Libraryに
チェックを入れます。
データを更新するコードは以下になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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のデータを見てみると、カレーの値段・データ更新日・データ更新者が
更新されています。
コメント