How to Fix Access Row Versioning Data Has Been Changed Error
Question details
The user encounters a row versioning error stating the data has been changed when updating records through an Access form linked to a SQL Server database.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A checkbox on an Access form is clicked, triggering an AfterUpdate procedure that saves the current record, updates related SQL Server records using dbSeeChanges, and then calls Me.Refresh.
- Observed behavior
- The update process works initially but randomly raises a data modification error, even though only one user is interacting with the database.
Ensure your SQL Server table includes a timestamp (rowversion) column to handle concurrency properly, and back up your Access frontend before modifying VBA code.
Replace Me.Refresh with Me.Requery in the AfterUpdate Procedure
Changing the form refresh method ensures Access retrieves the newly generated row versions from SQL Server after related records are modified.
When you execute an SQL UPDATE statement on related records while the current record is still being edited in Access, the local recordset loses synchronization with the SQL Server's rowversion column. Using Me.Refresh only updates the existing dataset without fetching the new row versions, leading to random concurrency errors.
By replacing it with Me.Requery, you force Access to completely re-fetch the underlying recordset, capturing the updated row versions and preventing the conflict error.
Open your Microsoft Access database, right-click the affected form in the navigation pane, and select Design View.
Select the checkbox control that triggers the error. Open the Property Sheet, navigate to the Event tab, and click the ellipsis (...) next to the On After Update property.
In the VBA Editor, locate the end of your update procedure where the Me.Refresh command is called after the dbSeeChanges query execution.
Delete Me.Refresh and type Me.Requery in its place. Save the VBA module and close the editor.
Switch the form back to Form View and trigger the checkbox update multiple times to verify that the random error no longer appears.
Analyze Exported Database Records with WPS Office
While resolving backend database errors requires Microsoft Access, WPS Office provides an excellent, free alternative for analyzing and reporting on the data you export from SQL Server. WPS Spreadsheet handles large datasets effortlessly while keeping your system running smoothly.
- 1. Download WPS Office: Visit the official WPS Office website and install the free suite on your device.
- 2. Open Exported Data: Launch WPS Spreadsheet and open the .csv or .xlsx file exported from your SQL Server database.
- 3. Analyze and Share: Use built-in sorting, filtering, and charting tools to analyze your data before sharing the report with your team.
Frequently Asked Questions
Why does Access say the data has changed when I am the only user?
This write-conflict error happens because Access and your background VBA SQL UPDATE query are both trying to modify records at the same time. Access detects the backend change and thinks another user caused it, triggering the row versioning conflict.
What is the difference between Refresh and Requery in Access?
Refresh updates the data currently displayed in the form but does not fetch new records or update internal row versions. Requery completely re-runs the form's underlying query, fetching fresh data, new row versions, and applying any additions or deletions.
Do I need a rowversion column in SQL Server for Access linked tables?
Yes, Microsoft Access relies on a timestamp or rowversion column to accurately determine if a record has been modified in SQL Server. Without it, Access compares every single field to check for changes, which can be slow and prone to errors.
