Needs help on ADP Form Optimization

[domengsi]

Hi,

Im using Access 2002 for front-end with SQL2000 back-end.

I have an "Orders" table with 60,000 records/orders and EACH order has 20 "item details" so for "order details" table I have 1,200,000 records in it.

Then I have an Order form, that is bounded to a query based on ORDERS table, with a subform in it. The subform displays the order details for the current Order Number. And also my "Orders" form have Link Child and Link Master fields base on the OrderNumber field in my tables.

My problem is when my program opens the Orders form its very slow it takes about 20 seconds to open my "ORDERS" form. And also when im navigating through it it takes about 5 seconds to move to the next order/record.


Is there any way to make my form loads faster?


thanks,
domengsi

 

[yaya013]

open the form with no records
and let the user to decide which record to show.

 

[BobJacksonNCI]

Tagging on to yaya013, make sure you have indexes on fields used by the query.

And, look at thread702-206410 just in case it applies to you.



HTH,
Bob

 

[domengsi]

i already set the form's property maxrecord property to 1
still it takes about 10 secs. i looked at the thread Thread702-206410 but i think the name auto correct option is only applicable to MDB and not in ADP(which im using). i can't find the options where to set the name autocorrect in ADP.


thanks.

 

[BobJacksonNCI]

Sorry, forgot which forum I was in.

The auto correct option is not available in Access Projects.

If your form is being supplied with all of your Orders records, the network is quite busy with your database alone. If you can partition the order number and then use a Stored Procedure with parameter to cut down the number of records passed, it will help.



HTH,
Bob