VBA & Macro Problems

How to Handle CurrentDb.Execute Errors and Confirm Insert Success in Microsoft Access

Ayan Masood Oct 1, 2026 869 views

Question details

The user needs to properly handle execution errors and verify the success of insert or update queries executed via CurrentDb.Execute in Microsoft Access.

How to Handle CurrentDb.Execute Errors and Confirm Insert Success in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Running SQL action queries through VBA where data integrity requires tracking successful record changes and catching potential execution failures.
Observed behavior: By default, Execute operations may fail silently without raising a visible error, and the exact number of modified rows remains unknown unless specific DAO properties and error handling structures are utilized.

Before you start

Ensure you are using the DAO data-access library in your VBA project, as the dbFailOnError option and RecordsAffected property are specific to DAO objects rather than ADO.

Solution 1Recommended

Use DAO with dbFailOnError and RecordsAffected

Implement structured VBA error handling and utilize DAO Database object properties to execute queries safely while verifying the exact number of changed rows.

To ensure action queries do not fail silently, you must append the dbFailOnError parameter to your execute statement. Combined with an On Error GoTo block, this allows Access to catch and report specific SQL errors.

Set up the error handler

At the beginning of your VBA procedure, add 'On Error GoTo ErrorHandler' to direct the code flow to a specific label if an error occurs.

Define the DAO database object

Declare a DAO Database object by writing 'Dim db As DAO.Database', and initialize it using 'Set db = CurrentDb()'.

Execute the action query

Run your SQL query string using the execute method with the failure flag: 'db.Execute strSQL, dbFailOnError'.

Verify affected records

Immediately after the execute statement, check the 'db.RecordsAffected' property. For example, use 'If db.RecordsAffected > 0 Then MsgBox db.RecordsAffected & " records successfully inserted."'.

Add a cleanup section

Before the error handler, add an exit label (e.g., 'ExitProc:') where you release memory by setting 'Set db = Nothing', followed by 'Exit Sub' or 'Exit Function'.

Create the error handler block

At the bottom of the procedure, add the 'ErrorHandler:' label. Include a message box like 'MsgBox "Error " & Err.Number & ": " & Err.Description' to report the failure, then resume to the exit label.

Use DAO with dbFailOnError and RecordsAffected

Validation Complete: If the query encounters a key violation or lock issue, it will immediately jump to the ErrorHandler, preventing silent failures and incomplete data processing.

Solution 2

Wrap Multiple Operations in a DAO Transaction

Use database transactions when running multiple related action queries to ensure they all succeed together or are completely rolled back upon failure.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Office Tasks?

While Microsoft Access is designed for complex relational databases, handling everyday data analysis, reporting, and documentation is often easier in a streamlined environment. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office suite for your spreadsheet, document, and presentation needs.

1. Download the Installer: Visit the official WPS Office website and click on the Free Download button.
2. Install WPS Office: Run the downloaded setup file and follow the on-screen instructions to install the suite.
3. Manage Data Easily: Open WPS Spreadsheet to import your exported Access data, utilize pivot tables, and manage your datasets seamlessly.

Seamlessly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Includes powerful Spreadsheet tools with advanced formulas, pivot tables, and VBA macro support for robust data management.Completely free to use, lightweight, and fast to launch on multiple operating systems.

microsoft office alternative - wps office

Frequently Asked Questions

Why does CurrentDb.Execute fail silently without an error message?

By default, the Execute method in DAO does not throw an error if an action query fails (for instance, due to primary key violations or validation rules). You must append the dbFailOnError parameter to the execute statement to force Access to raise a run-time error.

What is the difference between CurrentDb.Execute and DoCmd.RunSQL?

CurrentDb.Execute runs action queries in the background without prompting the user with confirmation dialogs. DoCmd.RunSQL triggers the standard Access warning messages (like 'You are about to append 1 row(s)') unless warnings are explicitly turned off via DoCmd.SetWarnings False.

Can I use RecordsAffected with ADO connections in Access?

While ADO has a similar concept using the RecordsAffected variable as an output parameter in the Connection.Execute method, the Database.RecordsAffected property is specifically designed for DAO objects like CurrentDb. Ensure your variables and error handling match the specific data-access library you are utilizing.

Excel & Data Problems