Form takes 5 minutes to load, help!

[stevet]

Hi,

I have a form that looks at several linked
tables. The tables are located on a different
machine on our corporate intranet. One
of the tables has 300 or so records, another
has just over 1000 records. The form takes
over 5 minutes to load. Is it taking that
long because it has to load all those records, or is there something else going
on? Any ideas? I can copy the tables
to the intranet location in just a few seconds...

Thanks!

Steve

 

[Fornatian]

Access is a client side system, so yes you are right in saying that Access has to download the entire recordset to the client machine in order to fulfil the request to server. if you have a lot of combo boxes and list boxes on your form then this can also drag the process out. One suggestion I have to improve that is to only populate the combo boxes and list boxes when they get entered, that way they are only requesting info when required - so instead of having the control source permanently set, only call the SQL that returns the recordset when needed.

HTH

 

[stevet]

Are there any examples here you
could point me to that would explain
how to only load the combo box data
when it needed to be updated?

Steve

 

[Pat Hartman]

If you are using any domain functions such as DLookup(), DSum(), etc., keep in mind that each row will cause the domain function to be reexecuted. This means that a 1000 row table that uses a DLookup against a 300 row table will actually cause 300,000! rows to be transferred.

DLookup() in particular can almost always be eliminated via a join to the look up table. So, in the above example, only 1,300 rows would need to be transferred.

 

[Fornatian]

As I recall you can use the OnEnter event of the combo box to set the controlsource property:

Private Sub MyTextBox_Enter
MyTextBox.ControlSource = "Countries"
End sub.

This will set the combo box to look at the table Countries. Also you can set it to retrieve an SQL statements results.

Private Sub MyTextBox_Enter
MyTextBox.ControlSource = "Select * from Countries"

When doing this you have to either preset the no. of columns and widths or do it at run-time.

Before doing any of this I would look at Pat's suggestion about eliminating Domain functions.

Ian
End sub.

 

[stevet]

Thank you.

I'm not aware of having
used any domain functions... I'm new
to this and don't know what they'd
be used for. I'll try to load info
into the combo boxes with the enter
event though and see if that helps.

Thanks again,

Steve

 

[stevet]

To understand this a little better...

I developed the forms and tables in a single
db, then used the splitter utility to split
the tables from the queries and forms. I
then moved only the tables to the intranet server, thinking that the forms would reside locally, the tables on the server.

But the queries are on the local computer, and the forms use them--Are the queries requiring complete download of the tables? (I created these queries using Access' Wizards.)

Would there be a better way to arrange the elements of the db? Maybe put the queries with the tables, or not split the db at all (it will probably only have at max 10-15 users).

Thanks for your help,

Steve

 

[Pat Hartman]

The database should be split to make changing the front easier. Access is a file server not a database server. That means that to satisfy a query, the entire table needs to be sent to the local PC for processing. The behaviour does not change whether the database is split or not. Database servers such as Oracle and DB2 will process the query at the server and only return the result set to the local PC.

 

[stevet]

I understand, thank you.

Steve

 

[Peter Paul]

Another thought, something I have been using to help with remote users, but which works equally as well with folks on the LAN. I have set up some of my remote users with Terminal Services. That way the data processing is done on the server, and only the screen shots and key stroke changes are transmitted.

I assume that the other applications, Citrix or PCAnywhere would work equally as well.

Just a thought.

Peter