Excelで日々のデータを入力・集計し、その結果をマスターデータが保管されているAccessのデータベースに登録したい、という運用は非常に一般的です。VBAを使えば、Excelから直接Accessデータベースに接続し、新しいデータ(レコード)を追加する処理を自動化できます。
この記事では、VBAでAccessデータベースを操作するための主要な技術である**DAO (Data Access Objects)**を使い、Accessのテーブルに新しいレコードを1件追加するための基本的なコードと、その仕組みを解説します。
【重要】実行前の参照設定
DAOの機能をVBAで利用するには、事前に**「参照設定」**が必要です。この設定を行うことで、VBAがAccessデータベースの部品(オブジェクト)を正しく認識できるようになります。
- VBE(
Alt
+F11
)を開き、メニューのツール
>参照設定
をクリックします。 - 一覧の中から**「Microsoft Office XX.0 Access database engine Object Library」**を探し、チェックを入れて「OK」をクリックします。(XX.0の部分はお使いのOfficeのバージョンによって異なります)
完成したVBAコード
以下が、Accessデータベースに接続し、指定したテーブルに新しいレコードを追加するVBAコードです。
'参照設定: Microsoft Office XX.0 Access database engine Object Library
Sub AddNewRecordToAccess()
' DAOオブジェクト用の変数を宣言します
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dbPath As String
' --- 1. データベースに接続 ---
' データベースファイルのパスを指定
dbPath = ThisWorkbook.Path & "\SampleDB.accdb"
' 指定したデータベースを開く
Set db = DBEngine.OpenDatabase(dbPath)
' --- 2. テーブル(レコードセット)を開く ---
' "M_Employees"テーブルを開く
Set rs = db.OpenRecordset("M_Employees")
' --- 3. 新規レコードを追加して値を設定 ---
' 新しいレコードを追加する準備
rs.AddNew
' 各フィールドに値を設定
rs!EmployeeID = "EMP025"
rs!EmployeeName = "田中 太郎"
rs!Department = "営業部"
' レコードの変更を保存(確定)
rs.Update
' --- 4. オブジェクトを閉じて後片付け ---
' 開いた順とは逆の順で閉じる
rs.Close
db.Close
' オブジェクト変数を解放
Set rs = Nothing
Set db = Nothing
MsgBox "Accessデータベースにレコードを追加しました。"
End Sub
コードのポイント解説
① データベースへの接続
Set db = DBEngine.OpenDatabase(dbPath)
参照設定を行うと、VBAのプログラム全体を管理するDBEngine
オブジェクトが使えるようになります。その.OpenDatabase
メソッドに、接続したいAccessデータベースファイル(.accdb)のパスを渡すことで、データベース全体を操作するためのDatabase
オブジェクトを取得します。
② レコードセットを開く
Set rs = db.OpenRecordset("M_Employees")
次に、データベースオブジェクト(db
)の.OpenRecordset
メソッドを使い、操作したいテーブルの名前(例: "M_Employees"
)を指定します。これにより、テーブル内のレコードの集まり(レコードセット)を操作するためのRecordset
オブジェクトが取得できます。
③ 新規レコードの追加と値の設定
これがレコードを追加する一連の処理の核心部です。
rs.AddNew
rs!EmployeeID = "EMP025"
rs!EmployeeName = "田中 太郎"
rs.Update
.AddNew
: まず、.AddNew
メソッドを実行します。これは、テーブルに新しいデータを書き込むための「空の行(バッファ)」を用意する命令です。rs!フィールド名 = 値
: 用意された空の行の各フィールドに、!
(エクスクラメーションマーク)を使って値を設定していきます。rs!EmployeeID
は、「rs
レコードセットのEmployeeID
フィールド」という意味になります。.Update
: 全てのフィールドに値を設定し終えたら、最後に必ず.Update
メソッドを実行します。この命令により、バッファに書き込まれたデータが、実際のデータベースのテーブルに保存(コミット)されます。.Update
を忘れると、追加した内容はすべて破棄されてしまうので注意してください。
④ クローズ処理とオブジェクトの解放
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
データベースの操作が終わったら、必ず後片付けを行います。オブジェクトは開いた時とは逆の順序で閉じるのが基本です。まずレコードセット(rs
)を.Close
し、次にデータベース(db
)を.Close
します。最後に、各オブジェクト変数にNothing
を代入して、メモリを完全に解放します。
まとめ
DAOを使ってAccessにレコードを追加する手順は、以下の流れになります。
- OpenDatabase でデータベースに接続する。
- OpenRecordset でテーブルを開く。
- AddNew で新しい行の準備をする。
rs!フィールド名 = 値
でデータをセットする。- Update で保存を確定する。
- Close と Nothing で後片付けをする。
この一連の流れは、AccessデータベースをVBAで操作する際の基本パターンですので、ぜひ覚えて活用してください。