speeding up the code

[northernbeaver]

I have a form in an Access 2000 DB it has a Quote form that is huge to say the least, the control source of the form is a combination of about 6 big tables

(i.e SELECT

.*, Customers.*, Contacts.*, [Ship To Customers].*, [Materials Table].*,

.[Symtec Notes] FROM [Materials Table] RIGHT JOIN ([Ship To Customers] RIGHT JOIN (Contacts RIGHT JOIN (Customers INNER JOIN

ON [Customers].[CustomerID]=

.[CustomerID]) ON [Contacts].[ContactID]=

.[ContactID]) ON [Ship To Customers].[ShipToID]=

.[ShipToID]) ON [Materials Table].[MaterialID]=

.[MaterialID]; )


the problem Im having is that this form to open is VERY slow, about 30-45 seconds on a good day, does it help speed things up if you only select the fields of each table you actually need to show on the form?

 

[SQLBI]

Hi,

Requesting non required fields will definitely be slower than for example if you only need 2 from 100.

With the majority of smaller apps its not noticeable, but if your pulling off 10,000's of records when only 1000 will do it will make a difference.

Are you using the db on a network or locally as this will also effect transfer speeds, depending on the network traffic and your connection speed.

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[northernbeaver]

thanks for you're help but I seem to have a bit of a problem. This form has a LOT of objects on it and that seems to be where half my problems are steming from, I need to add two fields to this form but as soon as I do the form takes forever to load or just plain stops responding. Is there a limit to how many text boxes or objects can be on one form before degredation begins to happen and what is that limit? thanks

 

[BobJacksonNCI]

You may want to verify your "Name Autocorrect" setting.

Following is a copy of James Goodman's post - thread702-206410

jgoodman00 (Programmer) Feb 6, 2002
I posted a question a few months back regarding forms being slow to load. I have now found the reason (in my case) for this, & it is not what I expected.
I am using Access XP, but I believe this also applies to Access 2000.

Find the slowest loading form in your database & load it. Time how long it takes to load.

Next goto Tools-Options & select the General tab. On this tab there is an option group called 'Name Autocorrect'. If this is enabled, disable it.

Reopen your form. In the case of every database I have tried, this speeds up loading several times over!

It seems strange how this option can cause such a performance loss though...
James Goodman


HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.