question

Hartkamp avatar image
Hartkamp asked

Update via linked table fails , but via PassThrough works fine

I have an Access 2010 application that links to 10 tables on an SQL-server. One of these tables gives a problem with an update or delete done directly in the linked table. Insert works fine. When I start an Update query or a pass-through query it works OK as well, but when I open a DAO recordset and use rst.Edit and rst.Update it also gives the error. These 10 tables are all initialised during startup using an identical connection string, and the other 9 tables work fine. The error it gives translates to something like 'Another user has changed the record and therefore this update cannot be done' In the SQL server error log I can only see one difference when I compare updating the table that works to the one that doesn't. Log of successful update: 2013-09-20 23:19:01.09 spid53 ODS Event: language_exec : Xact 0 ORS#: 1, connId: 0 2013-09-20 23:19:01.09 spid53 Text:set implicit_transactions on 2013-09-20 23:19:01.09 spid53 Parameter# 0: Name=,Flags=0,Xvt=99,MaxLen=8096,Len=1962,Pxvar Value=UPDATE "dbo"."Prijsdetails" SET "ReferentiePrijs"=@P1 WHERE "ID" = @P2 AND "DealsID" = @P3 AND "Kwalificatie" = @P4 AND [...] 2013-09-20 23:19:01.09 spid53 Parameter# 22: Name=,Flags=0,Xvt=59,MaxLen=4,Len=4,Pxvar Value=21.033 2013-09-20 23:19:01.09 spid53 IPC Name: sp_executesql 2013-09-20 23:19:01.09 spid53 ODS Event: execrpc : Xact 0 ORS#: 1, connId: 0 2013-09-20 23:19:01.16 spid53 Xact BEGIN for Desc: 3500000006 2013-09-20 23:19:01.17 spid53 ODS Event: language_exec : Xact 0 ORS#: 1, connId: 0 2013-09-20 23:19:01.17 spid53 Text:IF @@TRANCOUNT > 0 COMMIT TRAN 2013-09-20 23:19:01.17 spid53 Xact COMMIT for Desc: 3500000006 Log of the other one that doesn't work: 2013-09-20 23:17:43.95 spid53 ODS Event: language_exec : Xact 0 ORS#: 1, connId: 0 2013-09-20 23:17:43.95 spid53 Text:set implicit_transactions on SET TEXTSIZE 2147483647 2013-09-20 23:17:43.95 spid53 Parameter# 0: Name=,Flags=0,Xvt=99,MaxLen=8096,Len=1980,Pxvar Value=UPDATE "dbo"."SalesDetails" SET "MecomsContractNummer"=@P1 WHERE "ID" = @P2 AND "C-ID" = @P3 AND "MecomsContractNummer" = [....] 2013-09-20 23:17:43.95 spid53 Parameter# 26: Name=,Flags=0,Xvt=104,MaxLen=1,Len=1,Pxvar Value=0 2013-09-20 23:17:43.95 spid53 IPC Name: sp_executesql 2013-09-20 23:17:43.95 spid53 ODS Event: execrpc : Xact 0 ORS#: 1, connId: 0 2013-09-20 23:17:43.95 spid53 Xact BEGIN for Desc: 3500000005 2013-09-20 23:17:43.95 spid53 ODS Event: language_exec : Xact 0 ORS#: 1, connId: 0 2013-09-20 23:17:43.95 spid53 Text:IF @@TRANCOUNT > 0 ROLLBACK TRAN 2013-09-20 23:17:43.95 spid53 Xact ROLLBACK for Desc: 3500000005 The only differences I can see are the SET TEXT SIZE in the beginning of the faulty one, and the Rollback, where the correct one doesn't have the set text size and sends a Commit. I am stumped as to how two identical tables react differently and I don't know enough about these log-entries to know where to look for the solution. Any ideas ? Thanks in advance, Bas Hartkamp
updateodbc
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
and it's only breaking for the DAO recordset (like in one of your forms or modules)? Do you have the linked table opened directly in the editor while using the DAO recordset in another location?
0 Likes 0 ·
Hartkamp avatar image Hartkamp commented ·
The error occurs when the code runs, or when I open the table. These two are not related. The bit of the log shows the error occurring when I have (only) the table opened and try to edit a cell (any cell of any row). When I reproduced this error, nothing else was running or interfering in any way.
0 Likes 0 ·
Hartkamp avatar image Hartkamp commented ·
I am still trying to understand, but followed a colleague's advice to replace drop the table and create a new one. That fixed the problem, but recently it re-occurred somewhere else. This time though it only happens on some specific rows and not on all. Looks like there is an uncommitted update-transaction somewhere that I cannot find.
0 Likes 0 ·

1 Answer

·
Hartkamp avatar image
Hartkamp answered
Dropping the table and re-creating did work, but the problem re-emerged at another table in another database (same technique, same symtoms) The problem seems to be in the real or floating point fields: When these are compared they appear to have been changed, probably due to the way these are stored (difference on the umptieth digit). By adding a timestamp-type column the problem vanished. See http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-errors.html halfway on the page under 'modified record error'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.