Database slow when running query

[rolandj]

Good day,

Hopefully this is following the rules and in the right spot. I have been a self taught user of Access. I am using access 2003. I created a database for our company and it was working great. All the sudden when we try to run the one query it takes about a minute to run. It has been getting frustrating and i have no idea how to fix it. If anyone can look at the database to see if they can find the problem that would be great. It might be a mess as i learned when i needed to.

To get to the problem area:

First link the tables to the BE
1. Click Production
2. Click Enter Production
3. Click add record


4. Select a "Product", "Package Size", and "Brand".
5. The problem is the query on the after update event on brand.
6. Also when you input a quantity is stalls as well.

Any help would be appreciated.

Thanks in advance

[ranman256]

i have some users like this,it could be:
network speed
network usage
pc memory (a lot is better)

and if you do a lot of adding/subracting,compact the db.

[rolandj]

I have tried copying the back end to a stand alone PC and run it from there, but it is still slow. I have also cleared all the records being query except for one and it remains slow.

[JoeM]

Do you have primary keys and indexes in your tables?
See: https://support.office.com/en-us/art...a-38c6c4f1a0ce

Here is another article which goes over tips on how to improve performance (it is quite extensive!).
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

[rolandj]

Yes i do. I am not 100% sure if they are setup properly though.

[Bob Fitz]

Hi rolandj

Can you try the attached db (will need to be linked to your Backend file) and tell if it is any better than it was.

[rolandj]

Bob,

It is quicker, but says no record found. It would take forever before even if it couldn't find a record.

What this query is doing is pulling up the packaging materials required for the product and brand we are packing. There are individual consumer bags as well as a master container and pallet, so i have them setup in the defaults so whoever is entering the production doesnt put them under the wrong containers and throw our inventory out.

Thanks for the help so far

[Bob Fitz]

All the sudden when we try to run the one query it takes about a minute to run.

Which query is this. Did it ever run faster.

[rolandj]

It used to be quick. It just seemed like one day it decided to take forever. I have tried repairing and compacting the database with no luck. I have even removed all the defaults so it only had one record to pull up.
It is right after you update the brand that it runs and takes a long time to pull up the defaults for the product being packed. Also when you update the quantity, it runs slow as well

[Bob Fitz]

Do you have a backup of the db from a time when this problem didn't exist