MS access for multi-user inventory database RRS feed

  • Question

  • I am trying to set up an Access database for Inventory control and for multiple users. My idea was to enter and keep a record of inventory movments: additions, subtractions, loans, conversions (sale inventory to demo), etc. The current status of the inventory would then, for example available by adding up all movements for each item, plus I would retain a history of what happened over time.

    I am wondering, though, if that is compatible with multiple users. For example, to sell an item, I would have a temporary table with all items of gadget A (with serial numbers), and a user would then pick the right serial number and then that item would be sold. But if two people were selling items at the same time, would they interfere with each other? For example, the "items_temp" table would have to be cleared at the beginning, so if user 1 clears it then starts populating it with items, and then user2 starts selling and clears the temp table, it would delete everything that user1 had done. How do you avoid that in access?

    Tuesday, July 18, 2017 12:30 AM

All replies

  • Why do you think a "temp" table is needed? I'm 99% convinced it is not. Can you explain why that's better than a permanent table?

    I'm not 100% sure what your scenario is (not enough details about your db design), but let me speculate you have a Products table, and you have a ProductSerialNumbers table. Then you have a retail outlet where people buy those products, and you are required to record the serial number of each purchase. The clerk would look at the item, and use a barcode scanner or a keyboard to enter the serial number, then click Save to record the sale.

    Two clerks would presumably not enter the same serialno (but just in case: unique index); after all the two customers showed up at the checkout registers with two different items. Say the two numbers were entered, and Save was clicked at the same time.

    We then need to write an OrderDetail record with this sale to that table. It would have an extra SerialNo field.

    Given standard Optimistic Locking (and NO, pessimistic locking is not for you), the lock is only applied the few milliseconds it takes to save the record. But there is indeed a VERY SMALL chance that the two Saves would collide. This is where your error handler would kick in, and user2 would hit this error handler. Now it's up to you to decide how to handle it. One suggestion is to go to sleep for 100 msec and try again (Resume), up to ten times, before even showing any message to the user. More than likely the lock is long cleared up within this time and the second save will be successful. After that you could show a "database busy - wanna wait some more or Cancel"? message. It all depends on the many finer points of your solution that we are not privy to at this time.

    -Tom. Microsoft Access MVP

    Tuesday, July 18, 2017 2:28 AM
  • Hi Tom, thanks for responding.

    This is not a database for a checkout counter, where a customer would come to the register and present something with a serial number they want to buy. In this case the serial number needs to be picked out of a pool of serial numbers, and then the items are shipped later. So, clerk1 would run this and select a product with a serial number (plus probably some other items with or without a serial number) and someone else would take it from stock and ship it. Obviously, if another clerk has a similar sale, the product with the serial number that that clerk1 picked should no longer be available.

    Perhaps a temp table is not necessary, but this is what I was planning to do:

    My "transactions" table will contain the movements of inventory. In other words, it will have records that show when 5 gadgetA's came in, and when 3 of them were sold. For now I am keeping the serial numbers in that table (I know that is probably not the preferred way), so if GadgetA is erialized, there will be 5 entries in the "transactions" table, each one with a different serial number.

    For a sale, someone has to pick a serial number. I am trying to accomplish that by querying the "transactions" table for "GadgetA" and make a new table ("transactions_temp") with those entries. The user then picks the right serial number, and the record in the temp table is then changed (sales date, customer, etc.) and added back to the "tranasctions" table, the temp table is cleared, and I have a record of the transaction.

    The reason I am using the temp table is because a sale is usually not a single "Gadget", but could be multiple, with and without serial numbers, and so I prepare all transactions in the temp table and then update the permanent table once everything is done.

    But I am open to other suggestions, as access does not seem to be able to do this in a ulti-user setting. I ran the database on 2 computers, and even with record locking set to all, a second user would simply overwrite the temp table. So this may not even be feasible.

    Any suggestions are welcome.

    Tuesday, July 18, 2017 3:09 PM
  • Hi,

    Pardon me for jumping in... I also think a temp table may not be necessary, but we can't really say for sure without seeing your database. So, with regards to using a temp table, it should not be a problem in a multi-user environment if you split the database into a front and back end and kept the temp table in the front end (as a local table).

    Just my 2 cents...

    Tuesday, July 18, 2017 3:41 PM
  • Thanks DBguy.

    I'll check that out.

    Tuesday, July 18, 2017 3:57 PM
  • Thanks DBguy.

    I'll check that out.


    You're welcome. Just in case it might help, here's a Wiki article on how to split a database.

    Good luck!

    Tuesday, July 18, 2017 4:05 PM
  • The basic model for sales orders comprises four tables:


    If you are recording the serial number of each product sold, you could record each item sold as a single order line, and include the serial number as a column in the in the OrderDetails table, so if an order includes the sale of five widgets there would be 5 rows for that order in OrderDetails table, each with the same OrderId and ProductID fofeign key values, and five separate values in the SerialNumber column.  By indexing the SerialNumber column uniquely the same serial number cannot be entered more than once, so if two employees are trying to sell a product with the same serial number, the first employee to save the record would succeed, the second would incur an index violation error, which could be handled in the form's Error event procedure.

    The alternative, and probably the better method, would be to have a single line in OrderDetails, which would include a Quantity column in the usual way, for the sale of the five widgets, and to have a separate table with a composite foreign key of OrderID and ProductID referencing the composite primary key of OrderDetails.  This table would include a SerialNumber column, which would be its primary key.  So any attempt to sell the same item twice would incur a key violation error.  Where serial numbers are not recorded when making a sale no rows would be inserted into this table.

    Again no temporary table is required.

    Ken Sheridan, Stafford, England

    Tuesday, July 18, 2017 5:28 PM