Update Query

[Tunde Odesanmi]

I have a personnel system setup correctly in access. I'm
have trouble with the time it takes an update query to
process a particular transaction. Although it works but
it takes some couple of seconds before the field updates
reflects in the destination table.
I have table A(standing database) with lots of field
including a field called location. This table A is index
on staffno field. Another table called B has same
structure. But this is a dynamic database. The current
update in the [B.location] field is transfer to table A
([A.location]) after pushing a command button. It takes a
while before this updates works after several seconds,
i.e pushing or clicking the command button. Meanwhile
table B is not indexed, since we want to keep track of
personnel movement.
What else do I need to do to make the update query work
fast as clients here are impatient with slow updates. We
all have Pentium 1V based PC connected to Novell Server.
Again, the database has been splitted with the backend on
a folder in the server
Pls help

Tunde

 

[mark grizzle]

There are several things you could try, but without
knowing what's slowing it down it's hard to craft a
solution.

Are the join fields in your query indexed? Doing so
should.

Test the query by itself, does it take long to execute?
If not, then try changing the Refresh Interval setting in
the database's Tools - Options menu. Lowering the
interval will decrease the time for changes to be
displayed in forms and datasheets, but will also increase
network traffic.

If the query's slow, then consider replacing it with code
behind the form. This can be much faster than running a
query. If that's not an option, consider setting the
query's Use Transaction property to no. This can
sometimes speed execution by limiting the number of locks
requested.

Lastly, the form's design shouldn't maintain a connection
to the tables on the server, as this will slow
performance as more users are added. Instead, try loading
the required data from the server into local
editing/viewing tables, and connecting only as required
(moving to a new record, making changes, etc.).