One Laptop getting "Record has been changed by another user" error using Access frontend SQL backend
We are using MS Access frontend forms and a SQL backend for a Quality Control database. It's multi-user and we have one PC, a laptop, that is getting the "This record has been changed by another user since you started editing it" error. It's MS Office Plus Access 2013 and MS SQL Server Enterprise 2016 SP2 (13.0.5026.0).
Details
* Only on PC is having this problem
* It's not the same record every time
* The Access DB is on a shared server not on the local laptop
* All the users, including the one in question has access to Read/Modify/Write on that server share.
* It's an update record that's having the problem, we haven't tested an insert
* We split our time between working in the office and working from home through VPN
* The user is getting the error at both home and at work. Doesn't matter where they are.
What we have tried
* We have had the user try it on a different PC, and they didn't have any issues at all
* We have the BIT fields set to 0 instead of Null
* We have the Timestamp Field
What we haven't tried yet
* We have not tried the me.dirty = False before updating the record. Any validity in trying this for just one laptop?
* Had another user login to the problem PC and see if they get the error. Just thought of that as I was typing. Will test that out.
Questions
* Why only one PC? Shouldn't all of the other PCs have the same intermittent issue?
* Could it be something wrong with the ODBC connection on that PC and not the others? But I believe they are all setup the same. Am double checking to make sure.
* Could remoting in some days be the issue? But again, we all do it, but don't have issues. Could it have corrupted a file on that laptop?
* Is there anything else to look at on the Laptop?
* Is there anything else we could try to test?
Details
* Only on PC is having this problem
* It's not the same record every time
* The Access DB is on a shared server not on the local laptop
* All the users, including the one in question has access to Read/Modify/Write on that server share.
* It's an update record that's having the problem, we haven't tested an insert
* We split our time between working in the office and working from home through VPN
* The user is getting the error at both home and at work. Doesn't matter where they are.
What we have tried
* We have had the user try it on a different PC, and they didn't have any issues at all
* We have the BIT fields set to 0 instead of Null
* We have the Timestamp Field
What we haven't tried yet
* We have not tried the me.dirty = False before updating the record. Any validity in trying this for just one laptop?
* Had another user login to the problem PC and see if they get the error. Just thought of that as I was typing. Will test that out.
Questions
* Why only one PC? Shouldn't all of the other PCs have the same intermittent issue?
* Could it be something wrong with the ODBC connection on that PC and not the others? But I believe they are all setup the same. Am double checking to make sure.
* Could remoting in some days be the issue? But again, we all do it, but don't have issues. Could it have corrupted a file on that laptop?
* Is there anything else to look at on the Laptop?
* Is there anything else we could try to test?
Of what data types are the primary key columns? They must be of a precise data type. Is your "Timestamp" column a ROWVERSION?
Check the installed drivers, whether they are the same version.
Check the installed drivers, whether they are the same version.
Few things:
Each user should have their own copy of the Access Front End. It's not clear if this is the case.
Floats can also cause the issue. Also triggers. Are there any triggers on this table that may be modifying the record without Access's knowledge?
The other thing that it might be is simply the operation they perform; do they do the same work (i.e. on the same table(s)) as other users not having issues?
This is a good place to start. Check the driver they are using (SQL vs native) and the version as compared to the other PC's.
Jim.
* The Access DB is on a shared server not on the local laptop
Each user should have their own copy of the Access Front End. It's not clear if this is the case.
* We have the BIT fields set to 0 instead of Null
Floats can also cause the issue. Also triggers. Are there any triggers on this table that may be modifying the record without Access's knowledge?
The other thing that it might be is simply the operation they perform; do they do the same work (i.e. on the same table(s)) as other users not having issues?
* Could it be something wrong with the ODBC connection on that PC and not the others? But I believe they are all setup the same. Am double checking to make sure.
This is a good place to start. Check the driver they are using (SQL vs native) and the version as compared to the other PC's.
Jim.
Why only one PCIt is something environmental.
Is this PC working with a wired connection or WiFi? WiFi might be intermittent and that is causing a problem.
Uninstall/reinstall Office
Make sure the ODBC drivers are correct.
ASKER
When it's here it's wired, WiFi at home, can try to uninstall and reinstall, and I'll double check the ODBC drivers, thanks.
ASKER
That particular user has a different form than the rest and they are updating different fields. We put in a request to QC a Report so ours is an insert query, and once the user QC's the report, they go into their form and update fields on the record that we don't generally see on our form. I wondered if that could be an issue. Most of what they update are checkboxes that go to bit fields on the database. Which is why we tried the null to 0.
We have pulled everyone else out of the database except them, and they still get the error.
It is only one single form that we are in on either form. It's not a mulit-form update. Very simple table, very simple form. I'll look at the tables involved and see if it's an issue with one table vs another. But still, it's just one PC having the issue.
The user works remote until Monday, so I'll look at this and test some of this then. Thanks for the suggestions.
it's any record the user tries to update on their laptop.
Primary Key is a numeric ID, and I'll check that the timestamp is rowversion. I believe it is, but I'll double check that.
When we first created it Access was the frontend and backend, then we moved the tables to our SQL warehouse, and it became a frontend. Didn't think about everyone having their own copy of the frontend.... Could try that.
We have pulled everyone else out of the database except them, and they still get the error.
It is only one single form that we are in on either form. It's not a mulit-form update. Very simple table, very simple form. I'll look at the tables involved and see if it's an issue with one table vs another. But still, it's just one PC having the issue.
The user works remote until Monday, so I'll look at this and test some of this then. Thanks for the suggestions.
it's any record the user tries to update on their laptop.
Primary Key is a numeric ID, and I'll check that the timestamp is rowversion. I believe it is, but I'll double check that.
When we first created it Access was the frontend and backend, then we moved the tables to our SQL warehouse, and it became a frontend. Didn't think about everyone having their own copy of the frontend.... Could try that.
Didn't think about everyone having their own copy of the frontend.... Could try that.As Jim D said, that's very, very important.
To me, the biggest issue is trying to use Access with a WIFI connection. That's trouble enough, but adding a VPN into the mix is just begging for troubles. Access is intended to be run on a fast and wide wired connection. Anything else means you're way outside of the "norms" for Access, and you'll end up with issues like this.
We have pulled everyone else out of the database except them, and they still get the error.If there is only one user in the system and you get this message, then you are conflicting with yourself. That means that the code behind the form is running an update query that is trying to update the dirty form record.
If you can post the code behind the form, we might be able to point out the problem.
"numeric ID": Is it a integer or single/double?
Please note my comments at the top and also PatHartman's comment:
If changing the front-end application that is in use on the laptop causes the problem to disappear, irrespective of the type of network access, it will be a difference between the front-end that the user experiencing the problem uses and the front-end that everyone else who does not experience the problem has. What happens if you try the "problematic" front-end on a device that usually works (and ideally is connected (a) the same way as the laptop and additionally (b) cabled directly to the network)? If the problem re-occurs irrespective of connection type, it is the front-end application that needs to be examined more closely.
If there is only one user in the system and you get this message, then you are conflicting with yourself. That means that the code behind the form is running an update query that is trying to update the dirty form record.The root cause of the error you are seeing is that an update to a record is happening behind the scenes and as he says (and I eluded to in my first comment), the update that the user is attempting is in conflict that another that has occurred behind the scenes. A common cause is a trigger that updates data when a record is updated, or there may be some code that is running that updates the record that is being edited.
If changing the front-end application that is in use on the laptop causes the problem to disappear, irrespective of the type of network access, it will be a difference between the front-end that the user experiencing the problem uses and the front-end that everyone else who does not experience the problem has. What happens if you try the "problematic" front-end on a device that usually works (and ideally is connected (a) the same way as the laptop and additionally (b) cabled directly to the network)? If the problem re-occurs irrespective of connection type, it is the front-end application that needs to be examined more closely.
The problem with that contention though is that only one user is having this problem. If this was an inherit flaw in the app design, then other users should be seeing the same problem, unless this problem user is doing an operation that no one else does, or works with data that no one else does.
If none of that is true (others work with the same stuff), then it pushes it back to being an environmental problem.
Jim.
If none of that is true (others work with the same stuff), then it pushes it back to being an environmental problem.
Jim.
We need more details on this before we can take it further.
Jim.
Jim.
Vicki said
That particular user has a different form than the rest and they are updating different fields.From that I have assumed that this specific user is doing something different to all other users. As you say
If this was an inherit flaw in the app design, then other users should be seeing the same problem, unless this problem user is doing an operation that no one else does, or works with data that no one else does.My suggestion is that this one user or the form that they are using may well be doing something different, so it's worth checking.
Sorry, missed that comment. But still, in the original question:
So doubtful it's the form if they are using the same FE from both places (could be they just didn't encounter the problem though, so it being the form is still not out of question).
Hard to say what's going on...if it worked with an Access BE and now does not with SQL, it's probably the tables, but it could be as something as simple as not having the dbSeeChanges when executing an update in code.
Was the change to SQL recent or is just the problem recent? Any changes made in the database recently? Is the laptop new?
Those answers would tell you more where to look for the problem and I would construct a time line around when the problem started listing everything that was done/changed.
In the meantime I will run back through this to make sure I didn't miss anything else.
Jim.
* We have had the user try it on a different PC, and they didn't have any issues at all
So doubtful it's the form if they are using the same FE from both places (could be they just didn't encounter the problem though, so it being the form is still not out of question).
When we first created it Access was the frontend and backend, then we moved the tables to our SQL warehouse, and it became a frontend. Didn't think about everyone having their own copy of the frontend.... Could try that.
Hard to say what's going on...if it worked with an Access BE and now does not with SQL, it's probably the tables, but it could be as something as simple as not having the dbSeeChanges when executing an update in code.
Was the change to SQL recent or is just the problem recent? Any changes made in the database recently? Is the laptop new?
Those answers would tell you more where to look for the problem and I would construct a time line around when the problem started listing everything that was done/changed.
In the meantime I will run back through this to make sure I didn't miss anything else.
Jim.
@Jim I think knowing that when the user tried on a different PC, whether or not they used exactly the same form with the same configuration as the laptop, or something different, is key to identifying what is causing the issue. The message that the user is seeing is quite clear and non-ambiguous. The answer lies in the differences between what the user experiencing the problem is doing, or what their version of the form is doing that others are not. It's clear to me that you know more about this kind of configuration than I do, so I am just attempting to complement the posts here rather than detract from them. It's good to read your suggestions.
Another user, may mean VBA code running while a record is still modified by a user.
This may occur in a BeforeUpdate event procedure.
But when you say only this PC, that makes the problem local to that PC, and could be anything.
Copy the application from another PC and try.
This may occur in a BeforeUpdate event procedure.
But when you say only this PC, that makes the problem local to that PC, and could be anything.
Copy the application from another PC and try.
I am thinking that probably you are using Access with MsSQL BE but instead of Utilizing the power of SQL you are just using it a data repository via Linked Tables...this is a bad idea and a performance killer...to really check if something is wrong just fire SQL Server Management Studio or any other similar and run the Update ...normally it should work without an issue...
So instead trying to Update via the "usual" Update (QDE) just write the Update Statement as pure TSQL and execute it via a Passthrough Query.
Simply open QDE ...select PassThrough Query
paste your Update code
goto to Properties (Property Sheet)
select your DSN
and make sure
Return Records = No
After that you have a passthrough query that talks directly to your SQL
after that replace your code with
So instead trying to Update via the "usual" Update (QDE) just write the Update Statement as pure TSQL and execute it via a Passthrough Query.
Simply open QDE ...select PassThrough Query
paste your Update code goto to Properties (Property Sheet)
select your DSN
and make sure Return Records = No
After that you have a passthrough query that talks directly to your SQL
after that replace your code with
CurrentDb.QueryDefs("THENAMEOFYOURPASSTHROUGH").ExecuteThis question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
If only the user that sees the issue is working on the database, does it happen? If so, when? Do they have multiple forms open? Do these forms carry out updates on data that is shared between the forms? Are there any automated processes that may be updating data in the background? If so, can they be paused?
There is no real "generic" answer that can be given to you except to say that you need to know which record is failing the update and then work backwards to identify what else would have updated it.
Edit: Remember that when you say that it is not the same "record" that is being updated, its not about the record visible on a form, but the records that are updated by that form and there may be more database records involved than you think.