I am using Access 2010 to update a database originally created in Access 2000. The database keeps track of orders, purchase orders, inventory, etc.
The original database had fields in the products table for the quantity on hand, on order, etc. Every time a product was sold or purchased or any other change, the fields were updated through the code. I have been reading that that is not the most efficient way to handle the inventory data.
So, I have created functions to update the OnHand, OnOrder and OnHold amounts. They work and calculate correctly. I have used these functions to calculate the inventory amounts for our product list. This list is used when an employee needs to add a product to an order or a purchase order. The problem is that we have almost 10,000 items on our product list (don't ask). When you open the form to display the products and inventory information, it is very slow. Also, the form allows the user to narrow his search by entering product categories, etc. Every time that is done, the form recalculates again. It will be very difficult to use in a real-world environment because of the speed.
Is there any way to speed things up? The item ID field is indexed. I have thought of transferring data to a temporary table when the product form is opened and only have to calculate once, or could a data macro in the product table help? Could I use the function there to calculate the amount and requery the field when a change is made?
Any ideas?
The original database had fields in the products table for the quantity on hand, on order, etc. Every time a product was sold or purchased or any other change, the fields were updated through the code. I have been reading that that is not the most efficient way to handle the inventory data.
So, I have created functions to update the OnHand, OnOrder and OnHold amounts. They work and calculate correctly. I have used these functions to calculate the inventory amounts for our product list. This list is used when an employee needs to add a product to an order or a purchase order. The problem is that we have almost 10,000 items on our product list (don't ask). When you open the form to display the products and inventory information, it is very slow. Also, the form allows the user to narrow his search by entering product categories, etc. Every time that is done, the form recalculates again. It will be very difficult to use in a real-world environment because of the speed.
Is there any way to speed things up? The item ID field is indexed. I have thought of transferring data to a temporary table when the product form is opened and only have to calculate once, or could a data macro in the product table help? Could I use the function there to calculate the amount and requery the field when a change is made?
Any ideas?
