| Bug #46406 | Row cannot be located for updating. Some values may have been changed since it w | ||
|---|---|---|---|
| Submitted: | 27 Jul 2009 13:56 | Modified: | 9 Nov 2009 8:49 |
| Reporter: | rob van olst | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Windows (Vista) |
| Assigned to: | CPU Architecture: | Any | |
[28 Jul 2009 8:21]
Klaus Lozie
Same problem here, MySQL ODBC Driver 5.1 Access 2007, vba code .update doesn't work when the value is already in the table. Always Run-time error '3197' The Microsoft Office Access database engine stopped the process because you and another user are attempting to change the same data at the same time. I'm absolutely sure that there is no second user. .update works fine when it runs for the first time. As of second time -> always this error. Please help.
[28 Jul 2009 8:21]
Klaus Lozie
Already tried to add a timestamp field, but this didn't solve my problem.
[29 Jul 2009 7:04]
Tonci Grgin
Rob, Klaus, as stated in original report (Bug#7160), I will really need ODBC trace from Driver Manager to see what's actually happening. In the meantime, check on http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1: rsCustomers.CursorLocation = adUseClient rsCustomers.Properties("Update Criteria").Value = adCriteriaKey rsCustomers.Open "SELECT * FROM Customers", cnNWind, _ adOpenStatic, adLockOptimistic, adCmdText rsCustomers.Fields("CompanyName").Value = "Acme" rsCustomers.Update or search BugsDB regarding "Update Criteria" property.
[29 Aug 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[3 Nov 2009 21:28]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=48514 it was marked as duplicate of this one, re-open this bug so.
[9 Nov 2009 8:49]
Tonci Grgin
Well, this is not a bug... Left on it's own, *Access* forms rather insane update queries depending on *all* fields values, timestamps and who knows what. Please try to tell Access how to form UPDATE queries as described above:
rsCustomers.Properties("Update Criteria").Value = adCriteriaKey
and catch any error it might throw.
If you omit this, you'll just end up with updates really failing cause Access tried to exact-match floats, blob's and so on.
As for error being thrown, it is rather straight forward... you said you want to update row but updated_row_count returned was 0 (as there was nothing to update). This occurs since first option in DSN config ("Return Matching Rows") is not checked even we say clearly it should be in manual. If it was, MySQL server would return "found_rows" (1+) instead of "affected_rows" (0).
Description: When I try to update a record but there are no changes in the record the update fails with the following error text: "Row cannot be located for updating. Some values may have been changed since it was last read." As far as I can see there are two work-arounds: 1. Catch error number -2147217864 2. First check if there are changes in the record How to repeat: rs.Open("SELECT * FROM table WHERE id=1" , db.Conn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockPessimistic) With rs .Fields("table_field").Value = "foobar" .Update() End With If the information in the database is the same as what's in the update then the error occurs.