speeding up searchs

  • Thread starter Thread starter Zodiacal
  • Start date Start date
Z

Zodiacal

how can i speed my order form search's up? would it help to limit the amount
of records to the current order# the user is working on? if thats the case
then i would have to create my own search form insted of the built in access
search form. but im willing to do all that if thats the way i should go...
or does access not care if you put a specific where clause in the sql it
will allways download all the records no matter what from the server?
is it time to move to sql server?

i have 30k records now...
 
If we knew what you have, and specifically what kind of search you want to
do, and what you mean by "download all the records from the server" (implies
that you have a split database with the data tables shared on a server)?

If your criteria applies to an indexed field then only the index may have to
be retrieved to select the record in a "normal" split Access database, so
yes, it can be faster.

For some general topics on multi-user databases in Access, download my
presentation on the subject from
http://appdevissues.tripod.com/downloads.htm. For the best links on
multiuser performance and avoiding corruption, visit MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.

If followup or clarification is needed, please do so here in the newsgroup,
not by e-mail. Thanks.

Larry Linson
Microsoft Access MVP
 
Sometimes creating indexes on common search fields can speed up form
searches. However, creating to many indexes can hinder search time also.
Standard indexes Access creates automatically are the Primary Key, the
Foreign Key (assuming you have relationships set on your database) and
Zipcodes. If your users search more often on ClientName or something similar
then consider creating an index on that field. This can be done in the
design view of the table. There is an icon on the toolbar to open the index
window.

Another way is to create a query, using all the fields from the table and
attaching the query to your form instead of the table. Queries generally
execute faster than table searches.

Ensure you periodically Compact your database. Uncompacted databases can
slow down execution of searches.

There may be more that an MVP can suggest.
 
Back
Top