VBA & Macro Problems

How to Identify INSERT Query Validation Errors in Access VBA

Huma Ashraf Ch Oct 13, 2026 869 views

Question details

The user needs a method to pinpoint exactly which validation rules prevent specific records from being added when executing an INSERT query via Access VBA.

How to Identify INSERT Query Validation Errors in Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Importing or inserting batch records where uncontrolled source data might violate table validation rules.
Observed behavior: Access VBA reports the number of affected records but fails to clearly identify the specific validation errors or the exact records that failed during a bulk INSERT query.

Before you start

Review your destination table schema to identify primary keys, required fields, and custom validation rules so you can anticipate the exact types of constraints your source data might violate.

Solution 1Recommended

Process Records Iteratively with Error Logging

Execute data inserts one record at a time to catch, handle, and log specific validation errors for each individual failed record.

While running a bulk INSERT query is fast, it fails silently or generically when validation rules are broken. By looping through the source data one record at a time using VBA Recordsets, you can use structured error handling to trap the specific cause of failure, such as duplicate keys or type mismatches, without halting the entire process.

Set up a recordset loop

Open a DAO or ADO recordset for your source data (e.g., a CSV file or temporary table) and begin looping through it using a `Do While Not rs.EOF` block.

Implement VBA error handling

Add an `On Error GoTo ErrorHandler` statement right before attempting the `.AddNew` or `CurrentDb.Execute` method for the current row.

Log the specific error details

In your error handler block, capture `Err.Number` and `Err.Description`. Write these details, along with the unique identifier of the failing source record, into a dedicated error logging table.

Resume the processing loop

Use `Resume Next` or direct the code back to the loop's continuation point so the macro proceeds to evaluate and insert the remaining records.

Process Records Iteratively with Error Logging

Performance Impact: Row-by-row processing takes more execution time than a single bulk INSERT, but it is highly recommended when dealing with uncontrolled external data where accurate error diagnostics are crucial.

Solution 2

Use Transactions for Batch Inserts

Wrap bulk INSERT queries in a workspace transaction to capture database engine errors and ensure data integrity upon failure.

Free Microsoft Office alternative

Try WPS Office for Seamless Data and Macro Management

While Microsoft Access handles complex relational databases, many data tracking, cleansing, and VBA macro tasks can be efficiently managed using WPS Office Spreadsheets. It is a lightweight, free alternative that supports advanced data validation and VBA/macros for powerful data processing.

1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open your Spreadsheets: Launch WPS Spreadsheets and open your existing Microsoft Excel macro-enabled workbooks seamlessly.
3. Enable Macros and Automate: Access the Developer tab to enable VBA macros and continue running your automated data validation scripts without compatibility issues.

Free, lightweight, and fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .xls)Built-in VBA/Macro support for automating data entry and complex validation tasksUser-friendly interface with zero learning curve for traditional Office usersCross-platform availability allowing you to manage data on Windows, Mac, Linux, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why does CurrentDb.Execute not show validation errors by default?

By default, the `Execute` method in Access VBA runs silently. You must append the `dbFailOnError` parameter (e.g., `CurrentDb.Execute SQL, dbFailOnError`) to force Access to raise a runtime error when an INSERT operation violates table validation rules.

Can I find out exactly which field caused the validation error in a bulk insert?

During a bulk insert, the database engine generally reports only the first error it encounters and stops. To pinpoint the exact fields for multiple failed records simultaneously, you must process the data row-by-row and log the errors individually.

What are common validation errors encountered during Access INSERT queries?

Common issues include duplicate primary keys violating unique rules, attempting to insert null values into required fields, data type mismatches (such as inserting text into a numeric field), or violating custom table-level constraint rules.

Excel & Data Problems