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

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のデータを更新するには参照設定を行う必要があります。
参照設定の方法は以下をご覧ください。

VBAで参照設定を行う

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

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

Option Explicit
Sub registerMenu()
  
  Dim registerSht As Worksheet
  Set registerSht = ThisWorkbook.Worksheets("Register")
   
  Dim adoCON As New ADODB.Connection
  Dim dbPath As String
  dbPath = "C:\access-register-test\MenuData.accdb"
  adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & dbPath & ";"
  adoCON.Open
  
  Dim adoRS  As New ADODB.Recordset
  adoRS.Open "T_メニュー", adoCON, adOpenDynamic, adLockPessimistic
  
  Dim menuName As String
  Dim price As Long
  Dim personName As String
  
  menuName = registerSht.Range("A3")
  price = registerSht.Range("B3")
  personName = registerSht.Range("C3")
  
  adoRS.AddNew
  adoRS!メニュー名 = menuName
  adoRS!値段 = price
  adoRS!データ新規登録者 = personName
  adoRS!データ新規登録日 = Now()
  adoRS.Update

  adoRS.Close
  adoCON.Close
  
  Set registerSht = Nothing
  Set adoCON = Nothing
  Set adoRS = Nothing
  
  MsgBox "メニューを登録しました", vbInformation, "登録完了"
    
End Sub

データの登録テスト

登録ボタンを押した時にregisterMenuプロシージャを呼び出すように設定します。

登録ボタンをクリックすると入力したデータを登録します。

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

Accessのデータを見るとT_メニューにピザのデータが登録されています。