Solved

FIND function slow in Access 2003

Posted on 2004-08-21
7
334 Views
Last Modified: 2008-01-09
Hi,

Ive recently upgraded one of my large Access 2000 applications to 2003. It runs with a SQL server on the backend using linked tables.
As often as I tell them otherwise, my users prefer to do a find instead of a filter on certain forms. We have noticed that since the upgrade doing a find on an indexed field of a table since upgrading to 2003 has slowed enormously. The table in question has less than 40,000 records. Users stillusing 2000 on the same data are still much faster.
Anyone have any ideas?

Adam
0
Comment
Question by:adamy12
7 Comments
 
LVL 3

Expert Comment

by:gillgates
ID: 11862322
Use pass through query... in the query view go to "Query" -> "SQL Specific" -> "Pass Through"

This will do all the work on the server and not bring back the enter dataset and then do the query.
0
 

Author Comment

by:adamy12
ID: 11866446
Thanks for the help, but the find being used is the built in one (CTRL-F). The form is linked to a the table involved, but as I mentioned the same functionality was inifintely quicker than access 2003.
I haav added functionality to do a filter which is quick but everyone for some wierd reason likes the find function. Maybe because the filter returns a single record but after a find, you can move through all records in the recordset.
How would I change the find to a pass through query?  

Adam
0
 
LVL 3

Accepted Solution

by:
Catalys earned 250 total points
ID: 11874893
Depending on how you implemented your filter, you can achieve the same results...with more coding.

I would try these steps:
1 - Find which field is active when the Find/Filter button is clicked & save its name. This is the field that will be used for the filter.

2 - Clone the form recordset and use the FindFirst method.

This way you are not returning any subset, but simply pointing at the first record that matches the given criterion.

I would keep this method if it is faster than the other one.

HTH
0
 

Expert Comment

by:cstans
ID: 14067696
Was there any solution for this problem?
Every time i use the binocular (CTRL+F) to find a record in a table or in datasheet view in Access 2003 it takes forever. In access 2000 it was instantly.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now