[Excel VBA] How to Add a New Record to an Access Database (DAO)

2025年12月27日

It is a very common operation to enter and aggregate daily data in Excel and then register the results into a master database stored in Access. Using VBA, you can directly connect to an Access database from Excel and automate the process of adding new data (records).

In this article, I will explain the basic code and mechanism for adding a single new record to an Access table using DAO (Data Access Objects), a primary technology for manipulating Access databases via VBA.

[Important] Reference Setting Before Execution

To use DAO functions in VBA, you must first set a “Reference”. This setting enables VBA to correctly recognize Access database components (objects).

Open the VBE (Alt + F11) and click Tools > References in the menu.
Find “Microsoft Office XX.0 Access database engine Object Library” in the list, check the box, and click OK. (The “XX.0” part varies depending on your Office version).

Completed VBA Code

Below is the VBA code that connects to an Access database and adds a new record to a specified table.

' Reference: Microsoft Office XX.0 Access database engine Object Library
Sub AddNewRecordToAccess()

    ' Declare variables for DAO objects
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim dbPath As String

    ' --- 1. Connect to the Database ---
    ' Specify the path of the database file
    dbPath = ThisWorkbook.Path & "\SampleDB.accdb"
    ' Open the specified database
    Set db = DBEngine.OpenDatabase(dbPath)

    ' --- 2. Open the Table (Recordset) ---
    ' Open the "M_Employees" table
    Set rs = db.OpenRecordset("M_Employees")

    ' --- 3. Add New Record and Set Values ---
    ' Prepare to add a new record
    rs.AddNew
    
    ' Set values for each field
    ' Syntax: rs!FieldName = Value
    rs!EmployeeID = "EMP025"
    rs!EmployeeName = "Taro Tanaka"
    rs!Department = "Sales"
    
    ' Save (Commit) the changes to the record
    rs.Update
    
    ' --- 4. Close Objects and Clean Up ---
    ' Close in the reverse order of opening
    rs.Close
    db.Close
    
    ' Release object variables
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "A record has been added to the Access database."

End Sub

Key Points of the Code

1. Connection to the Database

Set db = DBEngine.OpenDatabase(dbPath)

Setting the reference enables the DBEngine object, which manages the entire VBA database engine. By passing the path of the Access database file (.accdb) to its .OpenDatabase method, you acquire a Database object for manipulating the entire database.

2. Opening the Recordset

Set rs = db.OpenRecordset("M_Employees")

Next, use the .OpenRecordset method of the database object (db) to specify the name of the table you want to manipulate (e.g., “M_Employees”). This acquires a Recordset object for manipulating the collection of records within the table.

3. Adding a New Record and Setting Values

This is the core of the process for adding a record.

rs.AddNew
rs!EmployeeID = "EMP025"
rs!EmployeeName = "Taro Tanaka"
rs.Update

.AddNew: First, execute the .AddNew method. This command prepares a “blank row (buffer)” for writing new data to the table.
rs!FieldName = Value: Set values in the fields of the prepared blank row using the ! (exclamation mark/bang) operator. rs!EmployeeID means “the EmployeeID field of the rs recordset.”
.Update: After setting values for all fields, you must execute the .Update method at the end. This command saves (commits) the data written in the buffer to the actual database table. Note: If you forget .Update, all added content will be discarded.

4. Closing and Releasing Objects

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Always clean up after database operations are finished. The basic rule is to close objects in the reverse order they were opened. First, .Close the recordset (rs), then .Close the database (db). Finally, set each object variable to Nothing to completely release the memory.