Need Help on ADP form Optimization

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Need Help on ADP form Optimization

Author

Topic

domengsi
Starting Member

3 Posts

Posted - 2004-05-19 : 18:02:36

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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-19 : 19:18:30

Is the form a read-only? If so, you can use a stored procedure as the form's datasource.
Other options are:
Setting the MaxRecords to a more reasonable value (default is 10,000)
Changing the form's recordset type to Snapshot

You should probably think about adding a 'summary' form that displays a summary list of all your orders, then when the user clicks on an order it displays the detail. This makes tasks such as search and filter much easier to implement and more user-friendly.

HTH

Tim

domengsi
Starting Member

3 Posts

Posted - 2004-05-20 : 12:31:31

Hi Tim,

My Order form is not read-only it is used for adding/changing the record. I already have a Search/Summary form like what you said but in my Search form I have 2 options/buttons called FIND SELECTED RECORD/ORDER bitton and FIND ALL ORDERS button (displays/loads all the records in the list).

I already set the Order form's property MaxRecords to 1 (as its default value) and change the form's maxrecord value at runtime (in Form Open event)according to the value of MaxRecords entered by the user in the "User settings" table.

On the first test I ran the form with maxrecords set to 50 in User Settings it took 18 seconds to load. On the 2nd test tried to set the Maxrecords value to 0 to display all the records it took 25 seconds to load all the 60,000 records/Orders.

I also tried to change the form's recordset to snapshot like what you said, it took almost a minute to load the Order form.:(

Thanks,
domengsi

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-20 : 19:21:31

Try changing the source query. I assume you're linking the form to a table (Orders), but try changing the datasource to "SELECT fields... FROM ORDERS WHERE OrderID = xxxx"
But - you'll probably need to unlink the form, then re-link it in the OnLoad event handler.

If this doesn't work, I'm afraid you might need to convert the form into an unlinked form. This means populating the form on load and saving it again afterwards. This will also help for data integrity, as Access isn't the best when it comes to this. It should also increase performance and reliability of your system.

Tim

domengsi
Starting Member

3 Posts

Posted - 2004-05-25 : 17:00:02

I tried make the subform unbounded at design time and at run-time assigning the subform/SourceObject to it and it's still slow when it reach that code about 13-14 seconds overall.

here are some line of my codes:

/*this line code takes about 7 seconds to complete: */
DoCmd.OpenForm "frmOEOrder", , , , , acHidden, strCustID

/*this line code takes about another 6-7 seconds to complete: */
with me
.MaxRecords = UserSetting("intMaxrecords")
'assign new recordsource to form
.RecordSource = "SELECT * FROM qryOEOrder WHERE strTransactionType = 'Order' Or strTransactionType = 'Back Order'"
.Visible = True
end with

the line where i assign new RecordSource takes about 5 seconds to complete even if my Maxrecord is set to 50.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-25 : 19:39:00

Unless you can resolve the performance issue between the DB and your workstation, you'll probalby have to go for an unlinked form.
Just create a recordset containing the single record when the form opens and then populate the controls.
As I said, it's a bit more work but it will be more reliable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources