slow over network

[Ted]

I have database with 10 tables, the main one of which has 80 fields and
13000 records. There is a main form which shows data from the central table
and has about 20 calculated text boxes, some of whcih rely on quite complex
vba functions to calculate.

On a stand-alone this all works very quickly. But it needs to be deployed on
a network (I have split the data tables from the code) and it is incredilbly
slow. The problem seems to be the way I have designed a particular form and
hence I have two questions:

1. The form updates completely with the OnCurrent event.This means that a
tab control which is not seen when the form loads but takes a long time to
calculate mst calculate whenever the form opens. Can this calculation be
moved to another event so it is only calculated when required? I can'tt work
out which event to use.

2. If there is a found set with, say, 20 records whcih the user may need to
scroll through the form seems to work its way though all 20 records before
displaying the one required. This is a result of a WHERE caluse returning
the found set which meets the user's requirements and then a FIND FIRST
action which picks the one from the existing found set. Why doesn't it just
go to the record required without caluclating the others?

Clearly I am missing some basic understanding about events but my reading of
books, the help files and this newsgroup haven't shone the light. Yet.

Thanks in advance

Ted

 

[Nikos Yannacopoulos]

Ted,

There are a number of tricks you can apply, some of which make a huge
difference in performance of split apps. Check out this link (then you
can thank MVP Tony Toews!).

http://www.granite.ab.ca/access/performancefaq.htm

It has worked miracles for me (and many many others), with databases
much bigger than yours.

HTH,
Nikos

 

[NetworkTrade]

My guess (and it is only a guess) is that it is not the Event. Because the
Event is only the starting point. And I would guess that changing the
starting point will not change the speed of the process.

I would guess you need to consider a change to the process. If the code
doing the calculation resides in the desktop while the data it needs to
calculate resides in the BackEnd - and the network is slow...then getting the
data back & forth is the problem.

Can you load the data into the desktop temporarily somehow--- like maybe
into a temporary nonvisible form...and then orient the calculation to pull
the data from the local form rather than the remote Back End....

just thinking outload...may not be worth the hassle.....

 

[Jeff Boyce]

Ted

A couple points to add to the responses you've already received...

A table with 80 fields is probably not well-normalized. It probably
resembles a spreadsheet more than a relational database table. If this is
true, you're finding that it's hard to get Access to work well when you feed
it 'sheet data (sorry, couldn't resist!<g>).

If your forms/subforms are returning all records, this will take some time
over a network (it takes time on a local machine, too, but the pipe is much
fatter locally). An alternative approach is to refactor your forms so they
only return a single record. You can do this by providing a way to select
which record (i.e., a combo box).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/