How do you lock records in Access 2007
-
I have a VB.net app on about 10pc's that access an Access 2007 database on a server to control stock. The stock quantities are getting all messed up, and I cant find out why. I tried setting record locking from the Office button > Advanced Options > Record Locking (Default Record locking = Edited Record) but this seems to be more of a generic kind of setting, and not specific to a particular database - I.e. you can set this without opening a database. How do I ensure that a record is locked when executing a query?
-
I have a VB.net app on about 10pc's that access an Access 2007 database on a server to control stock. The stock quantities are getting all messed up, and I cant find out why. I tried setting record locking from the Office button > Advanced Options > Record Locking (Default Record locking = Edited Record) but this seems to be more of a generic kind of setting, and not specific to a particular database - I.e. you can set this without opening a database. How do I ensure that a record is locked when executing a query?
Could you post a code snipet of how you are updating the table ? For example, a statement like the following would be kind of hard to corrupt. update Inventory set qty_on_hand = qty_on_hand - qty_sold where item_code = "ABC123" Is it possible that you have two operators who say, "Oh yeah, we have 3 on hand" and each one tries to confirm an order for 2 items? This would leave you with a qty_on_hand = -1. The way around this would be to build a transaction that makes a request for 2 items to be sold and if it can't then rollback the request. (or create a back order). Something like: begin transaction update Inventory set qty_on_hand = qty_on_hand - qty_sold where item_code = "ABC123" if (select qty_on_hand from Inventory where item_code = "ABC123") < 0 then rollback Message to user: "Unable to complete transaction. Not enough qty on hand." else commit End if
-
Could you post a code snipet of how you are updating the table ? For example, a statement like the following would be kind of hard to corrupt. update Inventory set qty_on_hand = qty_on_hand - qty_sold where item_code = "ABC123" Is it possible that you have two operators who say, "Oh yeah, we have 3 on hand" and each one tries to confirm an order for 2 items? This would leave you with a qty_on_hand = -1. The way around this would be to build a transaction that makes a request for 2 items to be sold and if it can't then rollback the request. (or create a back order). Something like: begin transaction update Inventory set qty_on_hand = qty_on_hand - qty_sold where item_code = "ABC123" if (select qty_on_hand from Inventory where item_code = "ABC123") < 0 then rollback Message to user: "Unable to complete transaction. Not enough qty on hand." else commit End if
Hi David What you describe above is pretty much exactly the situation, except... A Sales Order typically has about 20 lines (I display the lines in a datagrid, they fill in the qty despatched for each item, and click confirm despatch. Two users can definitely try to despatch the same item at the same time. Then it gets worse. After they confirm the despatch... a) I update the Sales Order Header table - Status changes from Awaiting Despatch to Despatched b) For each row in the datagrid: i) See how much stock there is of the item in the stock table(Select query) ii) Update the Stock Table with new Qty (Update Query) ii) Write the qty despatched to the Sales Order Detail Table (Update Query) iii) Write a transaction to a stock History Table (Insert Query) iv) Write batch information to a Batch table Both your suggestions may help (i.e. instead of first running a select query to get the stock qty, calculating the new qty in the app, then doing a second query to update the table with the new value, to rather do the calculation in the query) This is a bit of a problem because of the way I store the stock qty - they have about 1000 stock items, that can exist in any of 10 warehouses. I only keep items that have stock in a warehouse in this table - kind of hard to explain here - perhaps i need to load every stock item in every warehouse in this table. Second I like the idea of a transaction rollback, but I need to rollback all the other table writes and updates as well? Is this possible. I can post code, but its long and would be much more than a snippet - can try to shorten it if this would help?
-
I have a VB.net app on about 10pc's that access an Access 2007 database on a server to control stock. The stock quantities are getting all messed up, and I cant find out why. I tried setting record locking from the Office button > Advanced Options > Record Locking (Default Record locking = Edited Record) but this seems to be more of a generic kind of setting, and not specific to a particular database - I.e. you can set this without opening a database. How do I ensure that a record is locked when executing a query?
Or Change your stock table to have an allocated field and increment/decrement that as the request is processed or cancelled/completed. It will depend on your business process, if the sales process takes minutes or a longer period this may suit. You have to deal with crash edge cases where you have orphaned allocated values.
Never underestimate the power of human stupidity RAH