【VBA】ExcelからADOでAccessにデータを登録する

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

概要

ExcelからADOでAccessにデータを登録してみます。

使用するAccessファイル

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

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

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

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

新規登録フォーム

今回はCさんがピザを880円で新規登録するとしてみます。

メニューをデータベースに新規登録したいのでデータ登録用のフォームをシートに用意します。

シート名はRegisterとします。

メニュー名、値段、データ新規登録者の名前を入力します。

A3セルに登録したいメニューを入力します。
B3セルにメニューの値段を入力します。
C3セルにデータ新規登録者の名前を入力します。

データ登録のVBAコード

参照設定

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

https://kazusa-pg.com/vba-references/

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

Accessにデータを登録するコードは以下になります。

 1Option Explicit
 2Sub registerMenu()
 3  
 4  Dim registerSht As Worksheet
 5  Set registerSht = ThisWorkbook.Worksheets("Register")
 6   
 7  Dim adoCON As New ADODB.Connection
 8  Dim dbPath As String
 9  dbPath = "C:\access-register-test\MenuData.accdb"
10  adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & _
11                            "Data Source=" & dbPath & ";"
12  adoCON.Open
13  
14  Dim adoRS  As New ADODB.Recordset
15  adoRS.Open "T_メニュー", adoCON, adOpenDynamic, adLockPessimistic
16  
17  Dim menuName As String
18  Dim price As Long
19  Dim personName As String
20  
21  menuName = registerSht.Range("A3")
22  price = registerSht.Range("B3")
23  personName = registerSht.Range("C3")
24  
25  adoRS.AddNew
26  adoRS!メニュー名 = menuName
27  adoRS!値段 = price
28  adoRS!データ新規登録者 = personName
29  adoRS!データ新規登録日 = Now()
30  adoRS.Update
31
32  adoRS.Close
33  adoCON.Close
34  
35  Set registerSht = Nothing
36  Set adoCON = Nothing
37  Set adoRS = Nothing
38  
39  MsgBox "メニューを登録しました", vbInformation, "登録完了"
40    
41End Sub