Recordset does not support updating

I saw them in a database from time to time, but I red somewhere in SQL BOL that it is a kind of short duration locks issued by update statement to maintain consistency and prevent deadlocks.

When I tried to find more information on update locks in internet, I ran into perfect article Read Committed and Updates by Craig Freedman.

Chances that these invent Trans records would be updated but some other user activity (not IC) are pretty thin: There is no much you can do with already financially updated inventory transfer transactions.

The only thing which is coming to my mind is an idea that some crazy user will start to change Invent Dim Id of inventory transactions via Item Details-Edit dimensions function.

But in complex queries, especially in queries with self-join, U-lock is placed in the very beginning of SQL Statement execution (when the page is being read for the first time) and kept until query reached a home-run stage, when all records to be updated are defined and the query started update phase itself.

Since our query in question has a join of 11 tables, even in the very best case scenario the system was taking a couple of minutes to evaluate all necessary info and proceed to update phase.

Maybe in your case, if you have many items with transfers, it will take about 30-40 minutes for execution of the whole logical block, but it yet much better than 30-40 minutes for one item dimension group.

BTW, there is no much point in locking here, because these updates being called only from starting part of Inventory Closing, which is executed in single user mode.Path & "\database\data.accdb" Set db = Open Database(db Location) sql = "Select * FROM [Master Table]" Set rs = db. Cells(2) & Chr(34) & "," str SQL = str SQL & "Field3 = " & Chr(34) & row. The simple thing in terms of design is that the extracted excel data mirrors the access db in structure so the update query should be simple. Open Database(str DBPath, bln Exclusive, bln Read Only, str Password) For Each row In Range("A1: C3").It turned out that update statement in SQL (and update_recordset statement in Axapta respectively) place U-lock ON EVERY row it read. If update statement has simple logic (like ), the system either update a row (if it fits a where condition) and replace U lock with X, either it immediately release U lock (if row does not fit to the condition).So for simple cases, U-lock is placed on a row for really short duration – from reading of a page into memory until end of the page’s in-memory scan.

Leave a Reply