How do I get rid of the slow response time when running a query?

[Guest]

Only Access (Pro 03) runs slows as far as my MS apps go. When I submit to a
query it takes about 20 seconds to respond......Help! It was always very
fast before.

 

[Joseph Meehan]

Only Access (Pro 03) runs slows as far as my MS apps go. When I
submit to a query it takes about 20 seconds to respond......Help! It
was always very fast before.

It would help if we knew more about the design, size etc of your
table(s) and query. Is it on a LAN or local machine, are there indexes?

 

[Guest]

Sorry about that.....ok here it goes.

My database holds about 187286 records, I create a new database for auditing
purposes every quarter.

This database is access by myself and my single coworker over a LAN
connection.

We use a query to look for s single record in a table with all of these
records.

When my coworker uses it there are no latency issues for him.

When I use it I have to wait about 15 - 20 seconds to query for a record.

I can open other databases from previous quarters and do not experience the
same issues.....

 

[Joseph Meehan]

Sorry about that.....ok here it goes.

My database holds about 187286 records, I create a new database for
auditing purposes every quarter.

This database is access by myself and my single coworker over a LAN
connection.

We use a query to look for s single record in a table with all of
these records.

When my coworker uses it there are no latency issues for him.

When I use it I have to wait about 15 - 20 seconds to query for a
record.

I can open other databases from previous quarters and do not
experience the same issues.....

Someone may have additional or better suggestions, but I would start by
suggesting that you split the database. Keeping the records themselves on
the LAN and accessing the data from a "Front end" database, linked to the
"Front end" database on the server with all the data on the server and
queries, reports etc on the Front ends.

Are the other databases located in the same directory? Is there a LDB
file with the same name as the database there before you open it?

 

[Guest]

Yes, there are several other databases in the same directory and the LDB does
not appear until after I open the DB I am working with.......to be fair I
have just recently taught myself enoug about access to use it for my
quarterly audits. The following are the only things that I ever do.

I create a single database for each gaming system and then one for the huge
employee listing file

In each of these DB I will create querys which become tables then I I end up
copying data from those tables into Excel reports and hadning them off to
whoever.

Each quarter I move all the db to another directory and start over. I
recently went to the SANS FIRE in DC and we downloaded all kinds of audit
tools but I still have a ton of space left on my laptop (28 gigs free).

It was not until I got back from DC that I had any issues. There was a
person who made a few adjustments to my laptop to speed up my Internet
connection as well.

Other than the tools that were installed and the adjustment to my Internet
setting there have been no other changes to my laptop.

 

[Joseph Meehan]

Yes, there are several other databases in the same directory and the
LDB does not appear until after I open the DB I am working
with.......to be fair I have just recently taught myself enoug about
access to use it for my quarterly audits. The following are the only
things that I ever do.

I create a single database for each gaming system and then one for
the huge employee listing file

In each of these DB I will create querys which become tables

Why make them tables? Don't store redundant information.

then I I
end up copying data from those tables into Excel reports and hadning
them off to whoever.

Each quarter I move all the db to another directory and start over. I
recently went to the SANS FIRE in DC and we downloaded all kinds of
audit tools but I still have a ton of space left on my laptop (28
gigs free).

Access can function well at almost 2 gig. Why do you start over so
often? Is you database that large?

It was not until I got back from DC that I had any issues. There was
a person who made a few adjustments to my laptop to speed up my
Internet connection as well.

Are you accessing the data via the internet? I believe you accessing it
via a LAN? Access does require a very good LAN connection. Even if the LAN
is good if your laptop is not really set up properly for it, Access will be
one of the things you would notice first as a problem.

Have you split the database yet? You should have the data stored on the
LAN in one MDB called the back end and you should have a second MDB file on
your laptop called a Front end (front and back are not the names of the
files, but just descriptions of the functions of the files) You want the
shared data on the back end and everything else (additional tables, forms,
reports and queries, on the front end located on your laptop.

 

[Tony Toews]

This database is access by myself and my single coworker over a LAN
connection.

We use a query to look for s single record in a table with all of these
records.

When my coworker uses it there are no latency issues for him.

When I use it I have to wait about 15 - 20 seconds to query for a record.

I can open other databases from previous quarters and do not experience the
same issues.....

I suspect this is the standard "second and subsequent user in gets
slow performance" problem. This is caused by Access opening the LDB
file many times during operations.

Create a dummy table in the MDB file and open it. Then run your
queries. And your coworker should do the same.

For more information on performance problems, other tips and links to
MS KB articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Thank you both so much. It looks like I need to go and take a class to learn
about the many functions of Access. I use multiple table becuase there are
different headings or columns for each application. I start all over again
each quarter because the information has to be effective during that quarter
and must be current.

 

[Tony Toews]

Thank you both so much. It looks like I need to go and take a class to learn
about the many functions of Access.

Nah, that's what we're here for. <smile> Seriously though classes
are good. However quite often you have specific questions which is
where we come in. Furthermore there aren't many classes at the
advanced level which would explain some of these performance issues.

Tony


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm