Speed up extensivd code

[northernbeaver]

The program I'm working on has been in operation for about 5 years now.' It was written in Access 97 and converted to Access 2000 with no problems. there is one form used for quoting. This form is fairly extensive as it links to about 4 tables. (Customer, Supplier, products, Quotes) Over time all these tables have gotten huge! The form is actually linked to the Quotes table and all the other tables are either data sources for dropdown boxes, or sub forms etc.... We have recently passed the 10,000 records in the quoting table and ever since then things have really slowed down. Users are complaining that dropdown boxes (customers) can take upto 1.5 minutes to open. filling out the form used to take 3 minutes, now takes about 7 to 10. any suggestions to speed things up?

 

[DanJR]

Hi there, i'm not sure how helpful I can be, but I guess there are a number of small things you may be able to do to 'speed up' your code. I guessing here becuase I don't know how your form works.

1. I would first compact and repair the database.

2. Re-evaluate the indices for each of the table. Create indexes for fields that you group by, search on, lookup, join on, etc. Although, creating indicies slow down record additions (because the index 'table' must be updated), it seems like your slow performance is due to the amount of data the form are 'look-ups' are based on.

3. Is it possible to minimise the amount of data that the form is based on? That is, load (filter) only the quote record that you are interested in, rather than the whole table.

4. With respect to combo box speed: Is it possible to filter the combo box to show only 'current customers'. Or, use a textbox to enter the cusomters name, then use code return a list of customers that 'match' the name displayed in the textbox. So, instead of having a combo box that lists all customers, you get the user to help you filter the list before search for the customer in the table.

5. Look for 'inefficent' code. As you probably know, some code is more efficient than others eg.

rs!name is better than rs.fields("name&quot

"for i = 1 to rs.recordcount " is faster than Do While Not rs.eof

--lots of small improvements in code can improve performance considerably

Sorry if i'm not much help...

Cheers,
DanJR