[Excel VBA] How to Execute SQL Commands (UPDATE/INSERT) on Access

2025年12月27日

When working with an Access database in VBA, you may want to “update all records that match a condition at once” or “add a new record directly using SQL” instead of processing records one by one.

For these types of data operations, the Database.Execute method is very powerful and efficient. It allows you to send SQL commands (Action Queries) directly to the database.

In this article, I will explain how to execute UPDATE and INSERT INTO SQL commands against Access from Excel VBA.

Important: Reference Setting Before Execution

To use DAO features, you must check “Microsoft Office XX.0 Access database engine Object Library” in the VBE under Tools > References beforehand.

Updating Records (UPDATE Statement)

Use the UPDATE statement when you want to change multiple records that match a specific condition at once.

Code Example (UPDATE)

For example, the following code raises the Salary by 5% for all employees with 10 or more YearsOfService.

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

    ' Declare variables
    Dim db As DAO.Database
    Dim sqlStatement As String
    Dim dbPath As String
    
    ' Specify the database path
    dbPath = ThisWorkbook.Path & "\EmployeeDB.accdb"
    ' Connect to the database
    Set db = DBEngine.OpenDatabase(dbPath)
    
    ' Create the UPDATE statement as a string
    sqlStatement = "UPDATE M_Employees SET Salary = Salary * 1.05 WHERE YearsOfService >= 10"
    
    ' Execute the SQL statement
    db.Execute sqlStatement, dbFailOnError
    
    ' Close the database
    db.Close
    
    ' Release the object
    Set db = Nothing
    
    MsgBox "UPDATE process completed."

End Sub

Key Points

Creating the SQL: First, store the UPDATE statement you want to run in a string variable (sqlStatement).
db.Execute Method: Simply pass the created SQL string to the .Execute method of the Database object, and the database engine will execute the command.
dbFailOnError Option: By specifying dbFailOnError as the second argument of .Execute, VBA will notify you with a runtime error if an error occurs during SQL execution. This makes it safer for transaction processing.

Adding Records (INSERT INTO Statement)

Use the INSERT INTO statement when you want to add a single new record.

Code Example (INSERT)

The following code adds one new employee record to the “M_Employees” table.

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

    ' Declare variables
    Dim db As DAO.Database
    Dim sqlStatement As String
    Dim dbPath As String
    
    ' Specify the database path
    dbPath = ThisWorkbook.Path & "\EmployeeDB.accdb"
    ' Connect to the database
    Set db = DBEngine.OpenDatabase(dbPath)
    
    ' Create the INSERT INTO statement as a string
    ' Break long SQL lines with " & _" for readability
    sqlStatement = "INSERT INTO M_Employees (EmployeeID, EmployeeName, Department) " & _
                   "VALUES ('EMP031', 'Jiro Suzuki', 'Accounting')"
                   
    ' Execute the SQL statement
    db.Execute sqlStatement, dbFailOnError
    
    ' Close the database
    db.Close
    
    ' Release the object
    Set db = Nothing

    MsgBox "INSERT process completed."

End Sub

Key Points

Creating the SQL: Construct the SQL using the syntax INSERT INTO TableName (FieldList) VALUES (ValueList).
String Quotes: Note that when specifying text (string) values within SQL, you must enclose them in single quotes (‘), like 'Accounting'.
db.Execute Method: Just like with UPDATE, use the .Execute method to run the SQL.

Summary

Compared to looping through a Recordset object to update data one by one, using the Database.Execute method is much faster and simpler, especially when processing large amounts of data at once.